Multiple Join and Union

Communicator
Tools
Profile Settings

Loading, please wait ...

Open its course - Database Systems Labs

Multiple Join and Union By Mukhtiar Zamin
Summary

Summary

4
Teachers with
Mukhtiar Zamin 19
Followers
For Learning All
Visibility
Security Status 2
Contributions
By Teachers

Notes

Students are auto-evaluated against related topics to ensure they learned it.

To introduce the concepts of multiple joins and union queries and how to use them.

Scope

Multiple joins
Union
Union all
Union with where
Join with update statement
Join with delete statement

Multiple Join

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.

SQL UNION Operator for All, By Mukhtiar Zamin

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

SQL UNION Syntax

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.

SQL UNION ALL Syntax

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;

SQL UNION ALL With WHERE

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;

Lab Tasks for All, By Mukhtiar Zamin

Update employee first name with office number 6.
Update customer with any a particular order number (you can select any order number).
Applying union print data of orders and order details table.
Applying union print a particular office number of an employee. You can select any office number you want.