SQL Studying Note I - Join

 SQL Join

      SQL Join are used very frequently to query data from 2 or more tables, base on the relationship between certain columns in these tables.

      Tables in a certain database are offen related to each other with keys.

      A primary key is a column(or a combination of columns) with a unique value for each row, each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data  in every table.

      Here are 2 demo tables: HEADER and ORDERHEADER

 

HID	STATUS	STATUSCHANGED	ORDERHEADER_HID
1	Submitted	02-7  -11 09.28.42.812000	1
2	Cancel	02-7  -11 09.28.42.812000	2
3	Complete	02-7  -11 09.28.42.812000	3

      Note that the "HID" column is primary key in the "HEADER" table. This means that NO two rows can have the same HID, THE HID dintinguishes two HEADER even if they have the same STATUS or STATUSCHANGED. ORDERHEADER_HID which is the column refer to ORDERHEADER table

      Next, we have the ORDERHEADER table:

HID	CUSTOMERREF	ORDERREF
1	Customer1	order0
2	Customer2	order1
3	Customer3	order2

      Note that the HID column is the primary key in the "ORDERHEADER" table either. and CUSTOMERREF and  ORDERREF is ORDERHEADER's attributes.

      Notice That: the relation between 2 tables above is the column "ORDERHEADER_HID"

 

Different SQL Joins

      Before we starting our sql examples, we will list the types of Join you can use, and the fifference between them.

      INNER JOIN: Return rows when there is at least one matchs in both tables.

      LEFT JOIN:   Return all rows from the left table, even if there are no matches in the right table.

      RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.

      FULL JOIN:   Return  rows when there is a match in one of the tables.

 

SQL INNER JOIN

      The SQL INNER JOIN keyword return rows when there is at least on matchs in both tables.

      The SQL INNER JOIN Syntax:

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

      The SQL INNER JOIN examples(base on above HEADER and ORDERHEADER tables):

SELECT ORDERHEADER.CUSTOMERREF, ORDERHEADER.ORDERREF, HEADER.STATUS HEADER_STATUS, HEADER.HID HEADER_HID FROM ORDERHEADER 
INNER JOIN HEADER ON ORDERHEADER.HID = HEADER.ORDERHEADER_HID;

     The Results:

 

CUSTOMERREF	ORDERREF	HEADER_STATUS	HEADER_HID
Customer1	order1	Submitted	1
Customer2	order2	Cancel	2
Customer3	order3	Complete	3

The SQL INNER JOIN keyword return rows when there is at least on matchs in both tables, if there are rows in ORDERHEADER table that do not have natchs in 'HEADER', those rows do not listed.

 

SQL LEFT JOIN

      The SQL LEFT JOIN keyword return all rows from left table(table_name1), even if there are no matches in the right table(table_name2).

      The SQL LEFT JOIN Syntax:

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

      The SQL LEFT JOIN Examples:

SELECT ORDERHEADER.CUSTOMERREF, ORDERHEADER.ORDERREF, HEADER.STATUS HEADER_STATUS, HEADER.HID HEADER_HID 
FROM ORDERHEADER LEFT JOIN HEADER ON ORDERHEADER.HID = HEADER.ORDERHEADER_HID 
ORDER BY HEADER_HID DESC;

      The Results:

 

CUSTOMERREF	ORDERREF	HEADER_STATUS	HEADER_HID
Customer3	order3	Complete	3
Customer2	order2	Cancel	2
Customer1	order3	Submitted	1

      The SQL LEFT JOIN keyword return all rows from left table(ORDERHEADER), even if there are no matches in the right table(HEADER). 

 

SQL RIGHT JOIN

      The SQL RIGHT JOIN keyword return rows from the right table(table_name2), even if there are no matches in the left table(table_name1).

      SQL RIGHT JOIN Syntax:

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

      The SQL RIGHT JOIN example;

SELECT ORDERHEADER.CUSTOMERREF, ORDERHEADER.ORDERREF, HEADER.STATUS HEADER_STATUS, HEADER.HID HEADER_HID 
FROM ORDERHEADER RIGHT JOIN HEADER ON ORDERHEADER.HID = HEADER.ORDERHEADER_HID 
ORDER BY HEADER_HID DESC;

 The Results:

 

CUSTOMERREF	ORDERREF	HEADER_STATUS	HEADER_HID
Customer3	order3	Complete	3
Customer2	order2	Cancel	2
Customer1	order3	Submitted	1

      The SQL RIGHT JOIN keyword return rows from the right table(HEADER), even if there are no matches in the left table(ORDERHEADER).

 

SQL FULL JOIN

      The SQL FULL JOIN Keyword return all rows when there is a match in one of the tables

      The SQL FULL JOIN  Syntax:

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

       The SQL FULL JOIN Example:

SELECT ORDERHEADER.CUSTOMERREF, ORDERHEADER.ORDERREF, HEADER.STATUS HEADER_STATUS, HEADER.HID HEADER_HID 
FROM ORDERHEADER FULL JOIN HEADER ON ORDERHEADER.HID = HEADER.ORDERHEADER_HID 
ORDER BY HEADER_HID DESC;

 The Results:

CUSTOMERREF	ORDERREF	HEADER_STATUS	HEADER_HID
Customer3	order3	Complete	3
Customer2	order2	Cancel	2
Customer1	order3	Submitted	1

       The FULL JOIN keyword returns all the rows from the left table (ORDERHEADER), and all the rows from the right table (HEADER). If there are rows in "ORDERHEADER" that do not have matches in "HEADER", or if there are rows in "HEADER" that do not have matches in "ORDERHEADER", those rows will be listed as well.

 

END: A More Complex SQL Query Example:

      Base on the above HEADER and ORDERHEADER tables, Search the all ORDERHEADER's Orderrefs Which  ORDERHEADER Orderref's value start with order0, or ORDERHEADER Orderref's value equal order1 or order2, and HEADER's STATUS's value is one of Submitted,Cancel,Complete, and  HEADER's STATUSCHANGED before '2011-12-14 07:31:00'?

      The Answer:

SELECT ORDERHEADER.ORDERREF FROM ORDERHEADER INNER JOIN HEADER ON ORDERHEADER.HID = HEADER.ORDERHEADER_HID 
WHERE (ORDERHEADER.ORDERREF = 'order1' OR ORDERHEADER.ORDERREF LIKE 'order0%' OR ORDERHEADER.ORDERREF = 'order2') 
AND (HEADER.STATUS = 'Cancel' OR HEADER.STATUS = 'Submitted' OR HEADER.STATUS = 'Complete') 
AND (HEADER.STATUSCHANGED < to_date('2011-12-14 07:31:00', 'yyyy-mm-dd hh24:mi:ss'));