To introduce the concepts of multiple joins and union queries and how to use them.
Syntax for multiple join is:
SELECT t1.col, t3.col FROM table1 join table2 ON table1.primarykey = table2.foreignkey
join table3 ON table2.primarykey = table3.foreignkey;
Example: Type the following query
Select customers.customerName, orderdetails.quantityordered from customers
inner join orders on customers.customerNumber=orders.customerNumber
inner join orderdetails on orders.ordernumber=orderdetails.ordernumber;
We first join table 1 and table 2 which produce a temporary table with combined data from table1 and table2, which is then joined to table3. This formula can be extended for more than 3 tables to N tables; you just need to make sure that SQL query should have N-1 join statement in order to join N tables. Like for joining two tables we require 1 join statement and for joining 3 tables we need 2 join statements.
The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
Example:
SELECT customers.customerName FROM customers
UNION
SELECT orders.orderDate FROM orders;
The following SQL statement uses UNION ALL to select all (duplicate values also).
SELECT customers.customerName, customers.addressLine1 FROM Customers
WHERE customerNumber=103
UNION ALL
SELECT orders.orderNumber, orders.orderDate FROM orders
WHERE orders.customerNumber=103;
To earn PASS status, system is about to evaluate you using multiple choice questions (MCQs) and True/False type questions.