Inner join and outer join

1. SQL KEYWORD INNER JOIN & OUTER JOIN

2. INTRODUCTION What is SQL ? SQL stands for Structured Query Language. SQL  is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS). SQL is used to communicate with a database and lets you access and manipulate databases.

3. INTRODUCTION (CON’T) SQL statements are used to perform tasks such as update data on a database , or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle , Sybase, Microsoft SQL Server , and Access.

4. INTRODUCTION (con’t) SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database What can SQL do ?

5. INTRODUCTION (con’t) What is a Relational Database System ? A relational database system contains one or more objects called tables. The data or information for the database are stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows.

6. INTRODUCTION (con’t) Columns contain the column name, data type, and any other attributes for the column. Rows contain the records or data for the columns. City High Low Manila 101 92 Makati 88 69 Quezon City 77 60 Sample Table “Weather Temperature” Columns Rows

7. SQL JOIN CLAUSE O ne of the most beneficial features of SQL & relational database systems Makes relational database systems "relational". Used to combine rows from two or more tables, based on a common field between them. A llows you to link data from two or more tables together into a single query result--from one single SELECT statement.

8. SQL JOIN CLAUSE ( con’t ) W hat would happen if you worked with one table only, and didn't have the ability to use “JOIN". Let's say you have a one-table database that is used to keep track of all of your customers and what they purchase from your store: CID_No Surname First Address Date Item Price

9. SQL JOIN CLAUSE ( con’t ) Every time a new row is inserted into the table, all columns will be updated, thus resulting in unnecessary redundant data. For example, every time Juan Tamad purchases something, the following rows will be inserted into the table: CID_No . Surname First Address Date Item Price 10908 Tamad Juan 14 Sipag St., Manila 01/07/14 1K Rice 40.00 10908 Tamad Juan 14 Sipag St., Manila 01/08/14 Purefoods Corned Beef 85.00 10908 Tamad Juan 14 Sipag St., Manila 01/09/14 555 Sardines 25.00

10. SQL JOIN CLAUSE ( con’t ) To eliminate unnecessary redundant data, our database should have two tables : One for keeping track of your customer And the other to keep track of what they purchase : CID_No Surname First Address CID_No Date Item Price TABLE 1 Cust_Info Table TABLE 2 Purchases Table Now, whenever a purchase is made from a repeating customer, the 2nd table, "Purchases" only needs to be updated , eliminating useless redundant data.

11. SQL JOIN CLAUSE ( con’t ) Notice that both tables have a common “ CID_No " column. CID_No Surname First Address CID _No Date Item Price TABLE 1 Cust_Info Table TABLE 2 Purchases Table CID_No This column, which contains the unique customer id number, will be used to  JOIN  the two tables.

12. INNER JOIN The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. SYNTAX SELECT  column_name (s) FROM  table1 INNER JOIN  table2 ON  table1.column_name = table2.column_name ; Notice that each of the columns are always preceded with the table name and a period. This is a good practice so that you wont confuse which columns go with what tables. It is also required if the name column names are the same between the two tables.

13. INNER JOIN ( con’t ) SELECT cust_info.first , cust_info.surname , purchases.item FROM cust_info INNER JOIN purchases ON cust_info.cid_no = purchases.cid_no ; To JOIN the Cust_Info Table and the Purchases Table using the CID_No column that is found in both tables, we have : EXAMPLE : column_name (s) table1 table 2 table1.column_name table2.column_name

14. INNER JOIN ( con’t ) If we execute these statements, using the data in the tables below : CID_No Surname First Address 10907 Clavio Arnold 7 Tahimik St, Quezon City 10908 Tamad Juan 14 Sipag St. Malate , Manila 10909 Santos Charo Laya Ave., Muntinlupa City TABLE 1 Cust_Info Table CID_No Date Item Price 10908 01/07/14 1K rice 40.00 10909 01/07/14 Papaya Soap 140.00 10907 01/07/14 1K Ampalaya 60.00 10908 01/08/14 Purefoods Corned Beef 85.00 10907 01/08/14 5K rice 350.00 10908 01/09/14 555 Sardines 25.00 TABLE 2 Purchases Table

15. INNER JOIN ( con’t ) We will have something like this : First Surname Item Arnold Clavio 1K Ampalaya Arnold Clavio 5K rice Juan Tamad 1K rice Juan Tamad Purefoods Corned Beef Juan Tamad 555 Sardines Charo Santos Papaya Soap

16. INNER JOIN ( con’t ) OTHER FORMATS TABLE ORDER : STATEMENT 1 STATEMENT 2 SELECT * SELECT* FROM Table1 FROM Table2 INNER JOIN Table2 INNER JOIN Table1 ON Table1.column _name ON Table2.column _name * Table2.column_name * Table1 . column_name

17. INNER JOIN ( con’t ) OTHER FORMATS SPECIFIED COLUMNS : SELECT specified_column /s FROM Table1 INNER JOIN Table2 ON Table1.column_name = Table2.column_name SELECT * FROM Table1,Table2 WHERE Table1.column_name = Table2.column_name ALTERNATIVE :

18. OUTER JOIN INNER JOIN requires the existence of a match between the tables based on specified column/s and only matching records are selected. The  OUTER JOIN keyword  does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists.

19. LEFT JOIN - Returns all rows from the left table for the first tables. Even if there are no matches in the right. OUTER JOIN ( con’t ) Depending on which table’s rows are retained, OUTER JOIN is subdivided into :

20. RIGHT JOIN - Return all the rows from the right table ( or the second table) even if there are no matches in the left table OUTER JOIN ( con’t )

21. FULL JOIN - Both tables are secondary (or optional) such that if rows are being matched in tabled A and table B then all rows from table A are displayed even if there is no matching row in table B and vice versa OUTER JOIN ( con’t )

22. Look at the tables below and note the common column names between them : EXAMPLE S: OUTER JOIN ( con’t ) EmployeeID EmpName 01-054 Jerry Mendoza 01-055 Dane Sevilla 01-056 Mylene Antonio LogID EmployeeID LogDate 1 01-054 11-09-11 2 01-056 11-10-11 3 01-054 11-11-11 TimeRecID LogID TIn TOut 1 1 09:00 18:03 2 2 07:30 16:55 3 3 09:02 18:34 TABLE 1 Employee Table TABLE 2 LogRecordsTable TABLE 3 TimeRecordsTable

23. OUTER JOIN ( con’t ) SELECT * FROM Employee AS e LEFTJOIN LogRecords AS L ON e.EmployeeID = L.EmployeeID LEFT JOIN TimeRecords AS t ON t.LogID = L.LogID LEFT JOIN EXAMPLE 1: ILLUSTRATION

24. OUTER JOIN ( con’t ) EmployeeID EmpName LogID EmployeeID LogDate TimeRecID LogID TIn TOut 01-054 Jerry Mendoza 1 01-054 11-09-11 1 1 09:00 18:03 01-054 Jerry Mendoza 3 01-054 11-11-11 3 3 9:02 18:34 01-055 Dane Sevilla Null Null Null Null Null Null Null 01-056 Mylene Antonio 2 01-056 11-10-11 2 2 7:30 16:55 OUTPUT NULL is returned where no there are no matches in the RIGHT table

25. OUTER JOIN ( con’t ) SELECT e.EmpName , L.LogDate , t.Tin , t.Tout FROM Employee AS e LEFTJOIN LogRecords AS L ON e.EmployeeID = L.EmployeeID LEFT JOIN TimeRecords AS t ON t.LogID = L.LogID LEFT JOIN EXAMPLE 2: OUTPUT EmpName LogDate TIn TOut Jerry Mendoza 11-09-11 09:00 18:03 Jerry Mendoza 11-11-11 9:02 18:34 Dane Sevilla Null Null Null Mylene Antonio 11-10-11 7:30 16:55

26. OUTER JOIN ( con’t ) SELECT e.EmpName , L.LogDate , t.Tin , t.Tout FROM Employee AS e LEFTJOIN LogRecords AS L ON e.EmployeeID = L.EmployeeID LEFT JOIN TimeRecords AS t ON t.LogID = L.LogID WHERE L.LogDate IS NOT NULL LEFT JOIN EXAMPLE 3: OUTPUT EmpName LogDate TIn TOut Jerry Mendoza 11-09-11 09:00 18:03 Jerry Mendoza 11-11-11 9:02 18:34 Mylene Antonio 11-10-11 7:30 16:55

27. OUTER JOIN ( con’t ) SELECT e.EmpName , L.LogDate , t.Tin , t.Tout FROM TimeRecords AS t RIGHT JOIN LogRecords AS L ON t.LogID = L.LogID RIGHT JOIN Employee AS e ON e.EmployeeID = L.EmployeeID RIGHT JOIN EXAMPLE 1: OUTPUT EmpName LogDate TIn TOut Jerry Mendoza 11-09-11 09:00 18:03 Jerry Mendoza 11-11-11 9:02 18:34 Dane Sevilla Null Null Null Mylene Antonio 11-10-11 7:30 16:55

28. OUTER JOIN ( con’t ) FULL JOIN : In FULL JOIN, both tables are secondary (or optional). In this case, if we are matching rows in table A and B, then we display : all rows from table A even if there is no matching row in table B all rows from table B even if there is no matching row in table A

29. Look at the tables below and note the common column names between them : EXAMPLE : OUTER JOIN ( con’t ) GenreCode GenreDesc HR Horror RC Romance HS History CY Comedy SF Sci-Fi MovieID MovieTitle GenreCode 1 Saw 4 HR 2 Bad Teacher CY 3 The Notebook RC 4 Shrek No code available 5 Schindler’s List HS 6 The Exorcist HR TABLE 1 MovieList TABLE 2 GenresTable

30. SELECT MovieTitle , GenreDesc FROM MovieList AS m FULLJOIN Genres AS g ON m.GenreCode = g.GenreCode FULL JOIN EXAMPLE STATEMENT : OUTER JOIN ( con’t ) MovieTitle GenreDesc Saw 4 Horror Bad Teacher Comedy The Notebook Romance Shrek NULL Schindler’s List History The Exorcist Horror NULL Sci-Fi OUTPUT

31. JOIN’s are used to combine & get the data from different tables. INNER JOIN returns rows when there is a match in both tables. LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. FULL JOIN returns rows when there is a match in one of the tables. SUMMARY

32. NULL values are returned in the output when no records are returned by an OUTER JOIN statement JOIN queries can be used with the conjunction of SELECT W e can also use WHERE clauses along with JOIN’s SUMMARY ( con’t )

33. WHAT HAVE YOU LEARNED ? Identify the type of JOIN shown in the following diagrams : 1 2 3 4

34. WHAT HAVE YOU LEARNED ? ( con’t ) Identify the following : This is used to communicate with a database and lets you access and manipulate databases. This is used to combine and get data from different tables. This returns rows when there is a match in one of the tables.

35. WHAT HAVE YOU LEARNED ? ( con’t ) 4. This returns rows when there is a match in both tables. 5. This returns all rows from the left table, even if there are no matches in the right table. This is where one or more objects called tables are contained. This allows you to link data from two or more tables together into a single query result from one single SELECT statement

36. WHAT HAVE YOU LEARNED ? ( con’t ) 8. This returns all rows from the right table, even if there are no matches in the left table. This is value returned in the output when no records are returned by an OUTER JOIN statement. This conjunction can be used with JOIN queries.

37. Thank You !!!