SQL Joins

1. SQL Joins Er. DharmeshDubey Sr. Lecturer Department of Information Technology SDBCT, Indore 10/10/2012

2. JOINS • Work with multiple tables as though they were a single entity. • Tables are joined on columns that have the same data type and data width.

3. Types of JOINS • INNER JOIN • OUTER JOIN • CROSS JOIN • SELF JOIN

4. INNER JOIN • Also known as EQUI JOIN • WHERE clause generally compares two columns from two tables with = operator. • Returns all rows from both tables where there is a match.

5. INNER JOIN SYNTAX 1 SELECT <column1>,<column2>,……..<columnN> FROM <table1> INNER JOIN <table2> ON <table1>.<column1>=<table2>.<column2> WHERE <condition> SYNTAX 2 SELECT <column1>,<column2>,……..<columnN> FROM <table1> ,<table2> WHERE <table1>.<column1> = <table2>.<column2>

6. INNER JOIN SELECT P.FirstName, O.OrderNoFROM Persons P INNER JOIN Orders OON P.P_Id=O.P_IdORDER BY P.LastName The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.

7. OUTER JOIN Select all rows from the table on the LEFT (or RIGHT or BOTH) regardless of whether the other table has values in common and (usually) enter NULL where data is missing.

8. Types of OUTER JOIN • The LEFT JOIN keyword returns all rows from the left table (table1), even if there are no matches in the right table (table2). • The RIGHT JOIN keyword returns all the rows from the right table (table2), even if there are no matches in the left table (table1). • The FULL JOIN keyword return rows when there is a match in one of the tables.

9. LEFT OUTER JOIN Syntax 1 SELECT <column1>,<column2>……<columnN> FROM <table1>LEFT JOIN<table2> ON <table1>.<column1>=<table2>.<column2> Syntax 2 SELECT <column1>,<column2>……<columnN> FROM <table1>LEFT JOIN <table2> WHERE <table1>.<column1>=<table2>.<column2>(+)

10. LEFT OUTER JOIN SELECT P.FirstName, O.OrderNoFROM Persons P LEFT JOIN Orders OON P.P_Id=O.P_IdORDER BY P.LastName The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).

11. RIGHT OUTER JOIN Syntax 1 SELECT <column1>,<column2>……<columnN> FROM <table1> RIGHT JOIN <table2> ON <table1>.<column1>=<table2>.<column2> Syntax 2 SELECT <column1>,<column2>……<columnN> FROM <table1>LEFT JOIN <table2> WHERE <table1>.<column1>(+)=<table2>.<column2>

12. RIGHT OUTER JOIN SELECT P.FirstName, O.OrderNoFROM Persons P RIGHT JOIN Orders OON P.P_Id=O.P_IdORDER BY P.LastName The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

13. FULL OUTER JOIN Syntax 1 SELECT<column1>,<column2>,…<columnN> FROM <table1> FULL JOIN <table2> ON <table1>.<column1>=<table2>.<column2> Syntax 2 SELECT <column1>,<column2>……<columnN> FROM <table1>LEFT JOIN <table2> WHERE <table1>.<column1>(+)=<table2>.<column2>(+)

14. FULL OUTER JOIN SELECT P.FirstName, O.OrderNoFROM Persons P FULL JOIN Orders OON P.P_Id=O.P_IdORDER BY P.LastName The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.

15. CROSS JOIN • The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product. • If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN. • An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

16. CROSS JOIN SELECT* FROM GameScores CROSS JOIN Departments

17. SELF JOIN A SELF JOIN is another type of join in sql which is used to join a table to itself, specially when the table has a FOREIGN KEY which references its own PRIMARY KEY.

18. SELF JOIN Company SELECT a.company_name,b.company_name, a.company_city FROM company a, company b    WHERE a.company_city=b.company_cityANDa.company_name<>b.company_name;