Joins

Views:
 
     
 

Presentation Description

Joins in SQL

Comments

Presentation Transcript

Displaying Data from Multiple Tables ( Joins ):

Displaying Data from Multiple Tables ( Joins ) By: Mr. Shehrevar Davierwala

Slide2:

Objectives After completing this lesson, you should be able to do the following: • Write SELECT statements to access data from more than one table using equality and non-equality joins • View data that generally does not meet a join condition by using outer joins • Join a table to itself

Slide3:

What Is a Join? Use a join to query data from more than one table. When data from more than one table in the database is required, a join condition is used. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, that is, usually primary and foreign key columns. TO display data from two or more related tables, write a simple join condition in the WHERE clause, in the syntax: Table1.column1 denotes the table and column from which data is retrieved Table1. column1 = table2. column2 is the condition that joins (or relates) the tables together.

Slide4:

Old Syntax Write the join condition in the WHERE clause. SELECT tablel.column , table2. column2 FROM table1, table2 WHERE tablel . columnl = table2. column2; ANSI Syntax Write the join condition in the ON clause. SELECT tablel.columnl , table2. column2 FROM table1 INNER JOIN table2 ON tablel . columnl = table2. column2; Prefix the column name with the table name when the same column name appears in more than one table. INNER JOIN (simple join/ Equi join)

Slide5:

Retrieving Records with Equijoins SELECT EMP.EMPNO, EMP.ENAME, EMP.DEPTNO, DEPT.DEPTNO, DEPT.LOC FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO EMPNO ENAME DEPTNO DEPTNO LOC 7698 BLAKE 30 30 CHICAGO 7369 SMITH 20 20 DALLAS 7499 ALLEN 30 30 CHICAGO 14 rows selected. • The SELECT clause specifies the column names to retrieve: - employee name, employee number, and department number, which are columns in the emp table - department number, department name, and location, which are columns in the DEPT table. The FROM clause specifies the two tables that the database must access: EMP table DEPT table The WHERE clause specifies how the tables are to be joined: EMP.DEPTNO=DEPT.DEPTNO

Slide6:

Using Table Aliases The following two scripts are equivalent. In the second one table aliases are used . SELECT e.ename , e.deptno , d.dname FROM emp e , dept d WHERE e.deptno = d.deptno ;

Slide7:

What is an outer join? Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. With an outer join the columns from the table where data is "missing" are returned as NULL values . Outer joins come in two basic flavours, called Left and Right. Left outer joins mean that the data must be contained in the table defined to the left side of the equivalence , but not necessarily the right hand side. Right outer joins, of course, work the other way around.

Slide8:

Outer Joins (Old usage) You use an outer join to also see rows that do not usually meet the join condition. Outer join operator is the plus sign (+). SELECT column-names FROM table-name1 FULL OUTER JOIN table-name2 ON column-name1 = column-name2 WHERE condition SELECT C.FirstName , C.LastName , C.Country AS CustomerCountry , S.Country AS SupplierCountry , S.CompanyName FROM Customer C FULL JOIN Supplier S ON C.Country = S.Country ORDER BY C.Country , S.Country

Slide9:

Right Outer Join Syntax This type of join returns all rows from the RIGHT-hand table specified in the ON condition and  only  those rows from the other table where the joined fields are equal (join condition is met ). SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column; SELECT e.ename , e.deptno , d.dname FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno ;

Slide10:

Left Outer Join Syntax This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column; SELECT e.ename , e.deptno , d.dname FROM emp e LEFT [OUTER] JOIN dept d ON e.deptno = d.deptno ;

Slide11:

Self JOIN A self JOIN occurs when a table takes a 'selfie'. A self JOIN is a regular join but the table is joined with itself. This can be useful when modeling hierarchies. They are also useful for comparisons within a table. SELECT column-names FROM table-name T1 JOIN table-name T2 WHERE condition T1 and T2 are different table aliases for the same table  SELECT B.FirstName AS FirstName1, B.LastName AS LastName1, A.FirstName AS FirstName2, A.LastName AS LastName2, B.City , B.Country FROM Customer A, Customer B WHERE A.Id <> B.Id AND A.City = B.City AND A.Country = B.Country ORDER BY A.Country

authorStream Live Help