![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » DBISAM Technical Support » Product Manuals » DBISAM Version 4 Manual for Delphi 5 » SQL Reference » SELECT Statement |
SELECT [DISTINCT | ALL] * | column [AS correlation_name | correlation_name], [column...] [INTO destination_table] FROM table_reference [AS correlation_name | correlation_name] [EXCLUSIVE] [[[[INNER | [LEFT | RIGHT] OUTER JOIN] table_reference [AS correlation_name | correlation_name] [EXCLUSIVE] ON join_condition] [WHERE predicates] [GROUP BY group_list] [HAVING predicates] [[UNION | EXCEPT| INTERSECT] [ALL] [SELECT...]] [ORDER BY order_list [NOCASE]] [TOP number_of_rows] [LOCALE locale_name | LOCALE CODE locale_code] [ENCRYPTED WITH password] [NOJOINOPTIMIZE] [JOINOPTIMIZECOSTS] [NOWHEREJOINS]
SELECT CustNo, Company FROM Orders
SELECT Customer.CustNo AS "Customer #", Customer.Company AS "Company Name", Orders.OrderNo "Order #", SUM(Items.Qty) "Total Qty" FROM Customer LEFT OUTER JOIN Orders ON Customer.Custno=Orders.Custno LEFT OUTER JOIN Items ON Orders.OrderNo=Items.OrderNo WHERE Customer.Company LIKE '%Diver%' GROUP BY 1,2 ORDER BY 1
INTO destination_table
SELECT * INTO "Results" FROM "Orders"
SELECT * INTO "Results" FROM "Orders"
SELECT * INTO "c:\MyData\Results" FROM "Orders"
SELECT * INTO "\MyRemoteDB\Results" FROM "Orders"
SELECT * INTO "\Memory\Results" FROM "Orders"
FROM table_reference [AS] [correlation_name] [, table_reference...]
SELECT * FROM "Customer"
SELECT * FROM "\Memory\Customer"
SELECT c.CustNo AS "Customer #", c.Company AS "Company Name", o.OrderNo "Order #", SUM(i.Qty) "Total Qty" FROM Customer AS c LEFT OUTER JOIN Orders AS o ON c.Custno=o.Custno LEFT OUTER JOIN Items i ON o.OrderNo=i.OrderNo WHERE c.Company LIKE '%Diver%' GROUP BY 1,2 ORDER BY 1
Join Type | Description |
Cartesian | Joins two tables, matching each row of one table with each row from the other. |
INNER | Joins two tables, filtering out non-matching rows. |
OUTER | Joins two tables, retaining non-matching rows. |
FROM table_reference, table_reference [,table_reference...]
FROM table_reference [INNER] JOIN table_reference ON predicate [[INNER] JOIN table_reference ON predicate...]
SELECT * FROM Customer c INNER JOIN Orders o ON (c.CustNo=o.CustNo)
SELECT * FROM Customer c JOIN Orders o ON (c.CustNo = o.CustNo) JOIN Items i ON (o.OrderNo = i.OrderNo)
SELECT * FROM Source s INNER JOIN Joining j ON (s.ID = j.ID1 || j.ID2)
FROM table_reference LEFT | RIGHT [OUTER] JOIN table_reference ON predicate [LEFT | RIGHT [OUTER] JOIN table_reference ON predicate...]
SELECT * FROM Customer c LEFT OUTER JOIN Orders o ON (c.CustNo = o.CustNo)
SELECT * FROM Customer c LEFT OUTER JOIN Orders o ON (c.CustNo = o.CustNo) LEFT OUTER JOIN Items i ON (o.OrderNo = i.OrderNo)
SELECT * FROM Source s RIGHT OUTER JOIN Joining j ON (SUBSTRING(s.ID FROM 1 FOR 2) = j.ID1) AND (SUBSTRING(s.ID FROM 3 FOR 1) = j.ID2)
WHERE predicates
SELECT Company, State FROM Customer WHERE State='CA'
SELECT Company, State FROM Customer WHERE (State='CA') OR (State='HI')
SELECT * FROM "Clients" C WHERE C.Acct_Nbr IN (SELECT H.Acct_Nbr FROM "Holdings" H WHERE H.Pur_Date BETWEEN '1994-01-01' AND '1994-12-31')
GROUP BY column_reference [, column reference...]
SELECT CustNo, SUM(ItemsTotal) FROM Orders GROUP BY CustNo
SELECT C.Company, SUM(O.ItemsTotal) AS TotalSales FROM Customer C, Orders O WHERE C.CustNo=O.CustNo GROUP BY C.Company ORDER BY C.Company
SELECT C.Company Co, SUM(O.ItemsTotal) AS TotalSales FROM Customer C, Orders O WHERE C.CustNo=O.CustNo GROUP BY Co ORDER BY 1
HAVING predicates
SELECT Company, SUM(sales) AS TotalSales FROM Sales1998 GROUP BY Company HAVING (SUM(sales) >= 1000) ORDER BY Company
SELECT Company, SUM(sales) AS TotalSales FROM Sales1998 WHERE (State = 'CA') GROUP BY Company HAVING (TOTALSALES >= 1000) ORDER BY Company
[[UNION | EXCEPT| INTERSECT] [ALL] [SELECT...]]
SELECT CustNo, Company FROM Customers EXCEPT SELECT OldCustNo, OldCompany FROM Old_Customers
SELECT S.ID, CAST(S.Date_Field AS TIMESTAMP) FROM Source S UNION ALL SELECT J.ID, J.Timestamp_Field FROM Joiner J
SELECT S.ID, S.Name FROM Source S INTERSECT SELECT J.ID, CAST('' AS CHAR(10)) FROM Joiner J
SELECT CustNo, Company FROM Customers UNION SELECT OldCustNo, Company FROM Old_Customers ORDER BY CustNo
ORDER BY column_reference [ASC|DESC] [, column_reference...[ASC|DESC]] [NOCASE]
SELECT EXTRACT(YEAR FROM LastInvoiceDate) AS YY, State, UPPER(Company) FROM Customer ORDER BY YY DESC, State ASC, 3
TOP number_of_rows
LOCALE locale_name | LOCALE CODE locale_code
ENCRYPTED WITH password
NOJOINOPTIMIZE
JOINOPTIMIZECOSTS
NOWHEREJOINS
This web page was last updated on Thursday, November 16, 2023 at 10:39 AM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |