Joins

1. Joins 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_dateFROM suppliers, ordersWHERE 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:

2. We have a second table called orders with three fields (order_id, supplier_id, and order_date).It contains the following data: 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.

3. 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_datefrom suppliers, orderswhere 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.

4. The above SQL statement could also be written as follows:- select suppliers.supplier_id, suppliers.supplier_name, orders.order_datefrom suppliers, orderswhere 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:- • We have a second table called orders with three fields (order_id, supplier_id, and order_date).It contains the following data:-

5. If we run the SQL statement below: select suppliers.supplier_id, suppliers.supplier_name, orders.order_datefrom suppliers, orderswhere suppliers.supplier_id = orders.supplier_id(+); Our result set would look like this: 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.

6. 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 (+)  = DEPT.DEPTNO ; Returns all records from DEPT and only those records from EMP which matches the condition EMP.DEPTNO  = DEPT.DEPTNO

7. 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, DEPTWHERE EMP.DEPTNO  = DEPT.DEPTNO (+); Returns all records from EMP and only those records from DEPT which matches the condition EMP.DEPTNO  = DEPT.DEPTNO

8. 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.DepartmentID = department.DepartmentID;

9. 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.col1, B.col2, C.col3 from A, B, C               No  where condition  which returns 10x20x30=6000 records are result.

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

11. SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country AND F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID;

12. Employee Table after Self-join by Country:-

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

14. Thank You!!!