Week 15 - SQL Join Presentation PDF

INFMGMT – INFORMATION MANAGEMENT SYSTEM WEEK 15 SQL JOIN 1 OBJECTIVES Learn how to use the Join SQL statement Learn different types of SQL join Create SQL queries that join multiple tables...

INFMGMT – INFORMATION MANAGEMENT SYSTEM WEEK 15 SQL JOIN 1 OBJECTIVES Learn how to use the Join SQL statement Learn different types of SQL join Create SQL queries that join multiple tables 2 SQL JOIN SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. 3 DIFFERENT TYPES OF SQL JOINS Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables LEFT (OUTER) JOIN: Returns ALL records from the left table, and the matched records from the right table RIGHT (OUTER) JOIN: Returns ALL records from the right table, and the matched 4 records from the left table DIFFERENT TYPES OF SQL JOINS 5 tblStudent tblCourse 6 INNER JOINS Simplest type of join Also called: Equality join, Equijoin, Natural join VALUES in one table equal to values in other table SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 Syntax: INNER JOIN table2 ON table1.matching_column = table2.matching_column; table1: First table. table2: Second table matching_column: Column common to both the tables. Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN. 7 DISPLAY THE COURSE ID, NAMES AND AGE OF STUDENTS ENROLLED IN DIFFERENT COURSES. tblCourse tblStudent SELECT tblCourse.COURSE_ID, tblStudent.NAME, tblStudent.AGE FROM tblStudent INNER JOIN tblCourse ON tblStudent.ROLL_NO = tblCourse.ROLL_NO; 8 DISPLAY THE COURSE ID, NAMES AND AGE OF STUDENTS ENROLLED IN DIFFERENT COURSES. tblStudent tblCourse SELECT tblCourse.COURSE_ID, tblStudent.NAME, tblStudent.AGE FROM tblStudent INNER JOIN tblCourse ON tblStudent.ROLL_NO = tblCourse.ROLL_NO; 9 DISPLAY COLUMN, SEARCH COLUMN, JOIN COLUMN Display columns: appear in SELECT clause Search columns: appear in WHERE clause Join columns: primary key and foreign key column on which you join the tables. Linkage table: contains join column to link other tables through foreign key values. Why Is This Important? Display Columns: Control what the user sees in the result set. Search Columns: Filter the rows to ensure meaningful results. Join Columns: Connect tables to combine related data. Linkage Table: Handle complex relationships, especially many- to-many. 10  LEFT JOIN This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 LEFT JOIN table2 ON table1.matching_column = 11 table2.matching_column; tblStudent tblCourse SELECT tblStudent.NAME,tblCourse.COURSE_ID FROM tblStudent LEFT JOIN tblCourse ON tblCourse.ROLL_NO = tblStudent.ROLL_NO; 12 tblStudent tblCourse SELECT tblStudent.NAME,tblCourse.COURSE_ID FROM tblStudent LEFT JOIN tblCourse ON tblCourse.ROLL_NO = tblStudent.ROLL_NO; 13  RIGHT JOIN RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column; table1: First table. table2: Second table matching_column: Column common to both the tables. Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the 14 same. tblStudent tblCourse SELECT tblStudent.NAME,tblCourse.COURSE_ID FROM tblStudent RIGHT JOIN tblCourse ON tblCourse.ROLL_NO = tblStudent.ROLL_NO; 15 tblStudent tblCourse SELECT tblStudent.NAME,tblCourse.COURSE_ID FROM tblStudent RIGHT JOIN tblCourse ON tblCourse.ROLL_NO = tblStudent.ROLL_NO; 16  FULL JOIN FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values. Syntax: SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column; table1: First table. table2: Second table 17 matching_column: Column common to both the tables. tblStudent tblCourse SELECT tblStudent.NAME, tblCourse.COURSE_ID FROM tblStudent FULL JOIN tblCourse ON tblCourse.ROLL_NO = tblStudent.ROLL_NO; 18 SELECT tblStudent.NAME, tblCourse.COURSE_ID FROM tblStudent 19 FULL JOIN tblCourse ON tblCourse.ROLL_NO = tblStudent.ROLL_NO; END OF DISCUSSION