INNER and OUTER JOIN QUERIES
Here we have created three tables, one a list of customers and their Account numbers called oddly enough, "Customers" A table called "cust_invoice" listing invoices and using the customer account number as a foreign key with the "Customers" table. "catalogsales" which is a detail table for "cust_invoice" listing the individual items ordered by our customers on each invoice using the invoice number "InvoiceNo" as a foreign key.
Let us say that we want a list of customers that have ordered from us in the past. To find this out we can relate the table listing the customer accounts with the table listing the invoices by matching the primary key field "AcctNumber" in the Customers table with the foreign key field "AcctNumber" in the cust_invoice table
sqlite> SELECT Customers.AcctNumber, Customers.Custname FROM Customers, cust_invoice WHERE Customers.AcctNumber = cust_invoice.AcctNumber;
| AcctNumber | Custname |
| ------------ | ------------------------- |
| 130208 | Nike Missiles Inc |
| 130286 | Unisales Inc. |
| 130247 | Charlies Bakery |
| 130286 | Unisales Inc. |
SELECT DISTINCT
Notice that since Unisales Inc. has two invoice numbers associated with the same account number in the "cust_invoice" table that they are listed in the results twice. If they had 50 invoice numbers under the same account number then they would be listed 50 times. However we are only interested in just a list of customers that have ordered from us. Using the "SELECT DISTINCT" clause as shown below, enables us to eliminate the duplicate rows.
sqlite> SELECT DISTINCT Customers.AcctNumber, Customers.Custname FROM Customers, cust_invoice WHERE Customers.AcctNumber = cust_invoice.AcctNumber;
| AcctNumber | Custname |
| ------------ | ------------------------- |
| 130208 | Nike Missiles Inc |
| 130247 | Charlies Bakery |
| 130286 | Unisales Inc. |
Inner Join
The above query by the way is known as in Inner Join query where the only rows returned are ones in which both tables have fields that match the stated criteria. The following code will achieve the same result.
SELECT DISTINCT Customers.AcctNumber, Customers. Custname FROM Customers INNER JOIN cust_invoice ON Customers.AcctNumber = cust_invoice.AcctNumber;
NATURAL JOIN
A NATURAL JOIN will also work in the above example since the primary key and the foreign key in the two tables have the same name. Be very careful in using Natural Join queries in the absence of properly matched columns, a cartesian product will be produced.
SELECT DISTINCT Customers .AcctNumber , Customers .Custname FROM Customers NATURAL JOIN cust_invoice ;
Using Aggregate Functions
sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo,ItemNo,Price,Quan ,(Price*Quan) 'EXT' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber ;
| AcctNumber | Custname | InvoiceNo | ItemNo | Price | Quan | EXT |
| ---------- | ------------------ | ---------- | -------- | -------- | ------ | -------- |
| 130208 | Nike Missiles Inc | 21001 | 4501 | 13.53 | 10 | 135.3 |
| 130208 | Nike Missiles Inc | 21001 | 5700 | 24.95 | 12 | 299.4 |
| 130208 | Nike Missiles Inc | 21001 | 4437 | 6.53 | 4 | 26.12 |
| 130208 | Nike Missiles Inc | 21001 | 4551 | 13.53 | 10 | 135.3 |
| 130208 | Nike Missiles Inc | 21001 | 4502 | 17.95 | 10 | 179.5 |
| 130286 | Unisales Inc. | 21027 | 4501 | 13.53 | 10 | 135.3 |
| 130286 | Unisales Inc. | 21027 | 5700 | 24.95 | 17 | 424.15 |
| 130286 | Unisales Inc. | 21027 | 4437 | 6.53 | 25 | 163.25 |
| 130286 | Unisales Inc. | 21027 | 3570 | 291.32 | 2 | 582.64 |
| 130286 | Unisales Inc. | 21015 | 4501 | 13.53 | 3 | 40.59 |
| 130286 | Unisales Inc. | 21015 | 4502 | 17.95 | 10 | 179.5 |
| 130286 | Unisales Inc. | 21015 | 5390 | 1499.99 | 1 | 1499.99 |
| 130247 | Charlies Bakery | 21023 | 4502 | 17.95 | 27 | 484.65 |
| 130247 | Charlies Bakery | 21023 | 4501 | 13.53 | 10 | 135.3 |
| 130247 | Charlies Bakery | 21023 | 5700 | 24.95 | 7 | 174.65 |
| 130247 | Charlies Bakery | 21023 | 4437 | 6.53 | 15 | 97.95 |
GROUP BY
Let us say that we want to know what each customer spent. Well we could try to use the SUM aggregate function as we did to calculate the total value of the inventory.
sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo , SUM(Price*Quan) 'TOTAL' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber ;
| Customers.AcctN | Customers.Custname | catalogsales.In | TOTAL |
| --------------- | -------------------- | --------------- | ---------- |
| 130247 | Charlies Bakery | 21023 | 4693.59 |
This doesn't look right does it. All the line items were added together and incorrectly attributed to one customer.
If you want a query with an aggregate function to deliver multiple rows such as by invoice number or account number then you must use the GROUP BY clause and the appropriate column to group on. In the example below we are grouping by invoice number (InvoiceNo), since there are 4 distinct invoice numbers there are four line items in the query result
sqlite> SELECT Customers.AcctNumber,Customers.Custname ,catalogsales.InvoiceNo ,SUM(Price*Quan) 'TOTAL' FROM Customers ,catalogsales WHERE Customers.AcctNumber= catalogsales.AcctNumber GROUP BY InvoiceNo;
| Customers. | Customers.Custname | catalogsales.InvoiceNo | TOTAL |
| ---------- | ------------------------------ | ---------------------- | ---------- |
| 130208 | Nike Missiles Inc | 21001 | 775.62 |
| 130286 | Unisales Inc. | 21015 | 1720.08 |
| 130247 | Charlies Bakery | 21023 | 892.55 |
| 130286 | Unisales Inc. | 21027 | 1305.34 |
If we group by Account Number then we get a slightly different result. The invoice charges for each account will be added together yielding three rows since there were three customers that were invoiced.
sqlite> SELECT Customers.AcctNumber,Customers.Custname,catalogsales.InvoiceNo,SUM(Price*Quan) 'TOTAL' FROM Customers,catalogsales WHERE Customers.AcctNumber=catalogsales.AcctNumber GROUP BY catalogsales.AcctNumber;
| Customers. | Customers.Custname | catalogsal | TOTAL |
| ---------- | ------------------------------ | ---------- | ---------- |
| 130208 | Nike Missiles Inc | 21001 | 775.62 |
| 130247 | Charlies Bakery | 21023 | 892.55 |
| 130286 | Unisales Inc. | 21015 | 3025.42 |
Left Outer Join Select Query
A left outer join returns all the records from the table on the left side of the JOIN clause and only those records from the table on the right that match the specified criteria.
The objective of the following example is to view a list of all customers regardless of whether or not they have ordered from us and if they have, then return the dollar value of those orders. To do this it will be necessary to use an Outer Join Query. The Query on the left is "Customers" and all records will be listed with records from catalogsales being listed only if there is a match.
sqlite> SELECT Customers.AcctNumber,Customers.Custname,catalogsales.InvoiceNo,Price*Quan 'TOTAL' FROM Customers LEFT OUTER JOIN catalogsales ON Customers.Acctnumber=catalogsales.AcctNumber;
| AcctNumber | Custname | InvoiceNo | TOTAL |
| ---------- | ------------------------------ | ---------- | ---------- |
| 130169 | Acme Widgets | ||
| 130208 | Nike Missiles Inc | 21001 | 135.3 |
| 130208 | Nike Missiles Inc | 21001 | 299.4 |
| 130208 | Nike Missiles Inc | 21001 | 26.12 |
| 130208 | Nike Missiles Inc | 21001 | 135.3 |
| 130208 | Nike Missiles Inc | 21001 | 179.5 |
| 130247 | Charlies Bakery | 21023 | 484.65 |
| 130247 | Charlies Bakery | 21023 | 135.3 |
| 130247 | Charlies Bakery | 21023 | 174.65 |
| 130247 | Charlies Bakery | 21023 | 97.95 |
| 130286 | Unisales Inc. | 21027 | 135.3 |
| 130286 | Unisales Inc. | 21027 | 424.15 |
| 130286 | Unisales Inc. | 21027 | 163.25 |
| 130286 | Unisales Inc. | 21027 | 582.64 |
| 130286 | Unisales Inc. | 21015 | 40.59 |
| 130286 | Unisales Inc. | 21015 | 179.5 |
| 130286 | Unisales Inc. | 21015 | 1499.99 |
| 130325 | M.I. Sinform & Sons | ||
| 130364 | Big Dents Towing Inc. | ||
| 130365 | Weneverpay Inc |
sqlite> .width 12 25 12
sqlite> SELECT Customers.AcctNumber AS 'Acct Number', Customers.Custname AS 'Company', SUM(Price*Quan) AS 'Invoice Amt' FROM
Customers LEFT OUTER JOIN catalogsales ON Customers.Acctnumber = catalogsales.AcctNumber GROUP BY Customers.AcctNumber
ORDER BY SUM(Price*Quan)DESC,Customers.Custname;
| Acct Number | Company | Invoice Amt |
| ------------ | ------------------------- | ------------ |
| 130286 | Unisales Inc. | 3025.42 |
| 130247 | Charlies Bakery | 892.55 |
| 130208 | Nike Missiles Inc | 775.62 |
| 130169 | Acme Widgets | |
| 130364 | Big Dents Towing Inc. | |
| 130325 | M.I. Sinform & Sons | |
| 130365 | Weneverpay Inc |
Full Outer Join
A full outer join returns all the records from the tables being joined and matches them where it can based on the specified column(s).
There is currently no provision for the use of FULL OUTER JOIN in SQLite, however we can achieve the same functionality by using a UNION clause to tie together two LEFT OUTER JOIN queries that mirror each other.
SELECT * FROM table_name_1 LEFT OUTER JOIN table_name_2 ON id_1 = id_2 UNION SELECT * FROM table_name_2 LEFT OUTER JOIN table_name_1 ON id_1 = id_2 ;
sqlite> SELECT id_2,field_2,id_1,field_1 FROM tbl_2 LEFT OUTER JOIN tbl_1 ON id_2=id_1
...> UNION
...> SELECT id_2,field_2,id_1,field_1 FROM tbl_1 LEFT OUTER JOIN tbl_2 ON id_1=id_2;
| id_2 | field_2 | id_1 | field_1 |
| ---------- | ---------- | ---------- | ---------- |
| 99 | |||
| 100 | alpha | 100 | |
| 101 | bravo | 101 | |
| 102 | charlie | 102 | |
| 103 | delta |
UNION and UNION ALL
The use of the UNION clause allows the result sets of two or more SELECT queries to be combined. Used by itself UNION will eliminate duplicate rows and sort ascending based on first column values unless an ORDER BY statement is added to the last SELECT statement. UNION ALL will list each row returned by each SELECT statement.
sqlite> SELECT id_2,field_2,id_1,field_1 FROM tbl_2 LEFT OUTER JOIN tbl_1 ON id_2=id_1
...>UNION ALL
...>SELECT id_2,field_2,id_1,field_1 FROM tbl_1 LEFT OUTER JOIN tbl_2 ON id_1=id_2;
| id_2 | field_2 | id_1 | field_1 |
| ---------- | ---------- | ---------- | ---------- |
| 100 | alpha | 100 | |
| 101 | bravo | 101 | |
| 102 | charlie | 102 | |
| 103 | delta | ||
| 99 | |||
| 100 | alpha | 100 | |
| 101 | bravo | 101 | |
| 102 | charlie | 102 |
Sorting UNION Queries
sqlite> SELECT id_2,field_2,id_1,field_1 FROM tbl_2 LEFT OUTER JOIN tbl_1 ON id_2=id_1
...>UNION ALL
...> SELECT id_2,field_2,id_1,field_1 FROM tbl_1 LEFT OUTER JOIN tbl_2 ON id_1=id_2
...>ORDER BY id_1 DESC, field_2;
| id_2 | field_2 | id_1 | field_1 |
| ---------- | ---------- | ---------- | ---------- |
| 102 | charlie | 102 | |
| 102 | charlie | 102 | |
| 101 | bravo | 101 | |
| 101 | bravo | 101 | |
| 100 | alpha | 100 | |
| 100 | alpha | 100 | |
| 99 | |||
| 103 | delta |
More about UNION ALL
UNIONS can also be used to compile data from multiple tables and format the output. In the following example an invoice is created using six SELECT statements each generating 4 columns all joined together by a UNION ALL clause.
sqlite> SELECT 'Requisition #: ' ,'Requestor: ','Authorization:','Req Date : ' UNION ALL
...>SELECT ReqNumber, Requestor, Auth,ReqDate FROM ReqEquip WHERE ReqNumber= 1004 UNION ALL
...>SELECT ' ',' ', ' ',' ' UNION ALL
...>SELECT 'Stock Number' , 'Quantity', 'Cost', 'Ext' UNION ALL
...>SELECT StockNumber, Quantity,ItemCost,Quantity * ItemCost AS 'EXT' FROM ReqDetail
...>WHERE ReqNumber= 1004 UNION ALL
...>SELECT ' ',' ','Total Amount : ' ,SUM(Quantity * ItemCost) AS 'EXT' FROM ReqDetail
...> WHERE ReqNumber= 1004;
| Requisition #: | Requestor: | Authorization: | Req Date : |
| 1004 | Steve North | R. Perry Mgr | 2007/12/02 |
| Stock Number | Quantity | Cost | Ext |
| 75150 | 1 | 0.75 | 0.75 |
| 51002 | 12 | 0.75 | 9 |
| 43111 | 2 | 3.7 | 7.4 |
| 51001 | 3 | 0.75 | 2.25 |
| Total Amount : | 19.4 |
Colin Riley -- Updated March 2009 --