Joins A join is used to combine rows

Joins: -A join is used to combine rows from multiple tables. A join is performed whenever two or more tables is listed in the FROM clause of an SQL statement. Inner Join (simple join) Inner joins return all rows from multiple tables where the join condition is met. For example, SELECT suppliers. supplier_id, suppliers. supplier_name, orders. order_date FROM suppliers, orders WHERE suppliers. supplier_id = orders. supplier_id; This SQL statement would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables. Let's look at some data to explain how inner joins work: We have a table called suppliers with two fields (supplier_id and supplier_ name). It contains the following data:

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data: order_id supplier_id order_date 500125 10000 2003/05/12 500126 10001 2003/05/13 The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables.

• Outer Join: • This type of join returns all rows from one table and only those rows from a secondary table where the joined fields are equal (join condition is met). • For example: select suppliers. supplier_id, suppliers. supplier_name, orders. order_date from suppliers, orders where suppliers. supplier_id = orders. supplier_id(+); • This SQL statement would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal. • The (+) after the orders. supplier_id field indicates that, if a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

The above SQL statement could also be written as follows: select suppliers. supplier_id, suppliers. supplier_name, orders. order_date from suppliers, orders where orders. supplier_id(+) = suppliers. supplier_id • Let's look at some data to explain how outer joins work: • We have a table called suppliers with two fields (supplier_id and name). It contains the following data: supplier_id supplier_name 10000 IBM 10001 Hewlett Packard 10002 Microsoft 10003 NVIDIA We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data: -

order_id supplier_id order_date 500125 10000 2003/05/12 500126 10001 2003/05/13 If we run the SQL statement below: select suppliers. supplier_id, suppliers. supplier_name, orders. order_date from suppliers, orders where suppliers. supplier_id = orders. supplier_id(+); Our result set would look like this: supplier_id supplier_name order_date 10000 IBM 2003/05/12 10001 Hewlett Packard 2003/05/13 10002 Microsoft <null> 10003 NVIDIA <null> The rows for Microsoft and NVIDIA would be included because an outer join was used. However, you will notice that the order_date field for those records contains a <null> value.

Types of Outer Joins: 1)Right Outer Join 2)Left Outer Join 3)Full Outer Join 1)Right Outer Join: - Returns all records from table B and only those matching with the join operation from Table A (just the reverse of left outer join) For eg: SELECT * FROM EMP, DEPT WHERE EMP. DEPTNO (+) = DEPTNO ; Returns all records from DEPT and only those records from EMP which matches the condition EMP. DEPTNO = DEPTNO

2)Left Outer Join: Returns all records from table A and only those matching with the join operation from Table B For eg: SELECT * FROM EMP, DEPT WHERE EMP. DEPTNO = DEPTNO (+); Returns all records from EMP and only those records from DEPT which matches the condition EMP. DEPTNO = DEPTNO

3)Full Outer Join: full outer join combines the effect of applying both left and right outer joins. Example full outer join: SELECT * FROM employee FULL OUTER JOIN department ON employee. Department. ID = department. Department. ID; Department. Na Department. ID me Employee. Last. Name Employee. Department. ID Smith 34 Clerical 34 Jones 33 Engineering 33 Robinson 34 Clerical 34 John NULL Steinberg 33 Engineering 33 Rafferty 31 Sales 31 NULL Marketing 35

Cross Join: Join without filter conditions. A Cross Join is the Cartesian product or the result of all possible combinations of the rows from each of the tables involved in the join operation. This occurs when, no specific Join conditions (filters) are specified. For eg: there are 3 tables A, B and C with 10, 20 and 30 number of rows respectively. So a cartesian production would happen in the below scenario, Select A. col 1, B. col 2, C. col 3 from A, B, C No where condition which returns 10 x 20 x 30=6000 records are result.

4)Self Join: A self-join is joining a table to itself, as though joining two separate tables. This is referred to as a self-join. Example: • A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table.

Employee Table Employee. ID Last. Name Country Department. ID 123 Rafferty Australia 31 124 Jones Australia 33 145 Steinberg Australia 33 201 Robinson United States 34 305 Smith Germany 34 306 John Germany ------nn. NULL SELECT F. Employee. ID, F. Last. Name, S. Employee. ID, S. Last. Name, F. Country FROM Employee F, Employee S WHERE F. Country = S. Country AND F. Employee. ID < S. Employee. ID ORDER BY F. Employee. ID, S. Employee. ID;

• Employee Table after Self-join by Country: - Employee. ID Last. Name Country 123 Rafferty 124 Jones Australia 123 Rafferty 145 Steinberg Australia 124 Jones 145 Steinberg Australia 305 Smith 306 John Germany

Equi Join: • An equi-join, also known as an equijoin, is a specific type of comparatorbased join, or theta join, that uses only equality comparisons in the joinpredicate. Example of an equi-join: SELECT *FROM employee EQUI JOIN department ON employee. Department. ID = department. Department. ID; Returns data only from those records which matches the condition employee. Department. ID = department. Department. ID from both tables.

Thank You!!!