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.
sqlite>

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.
sqlite>

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
sqlite>

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
sqlite>

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>

We are not quite there yet but by using the SUM aggregate function and grouping by AcctNumber, we can achieve the desired result.

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
sqlite>

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
sqlite>

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
sqlite>

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
sqlite>


Colin Riley -- Updated March 2009 --