Scribd Logo
Upload

Welcome to Scribd!

  • 81 views

    Join

    Uploaded by

    Ch Zaid Raza
    This document discusses SQL joins, including: 1. Joins combine rows from two or more tables based on common fields. 2. The main types of joins are equijoins, non-equijoins, outer joins, and self joins. 3. Joins allow querying and retrieving data from multiple tables.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Join

    Uploaded by

    Ch Zaid Raza
    81 views23 pages
    This document discusses SQL joins, including: 1. Joins combine rows from two or more tables based on common fields. 2. The main types of joins are equijoins, non-equijoins, outer joins, and self joins. 3. Joins allow querying and retrieving data from multiple tables.

    Original Title

    join (1).pptx

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses SQL joins, including: 1. Joins combine rows from two or more tables based on common fields. 2. The main types of joins are equijoins, non-equijoins, outer joins, and self joins. 3. Joins allow querying and retrieving data from multiple tables.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    81 views23 pages

    Join

    Uploaded by

    Ch Zaid Raza
    This document discusses SQL joins, including: 1. Joins combine rows from two or more tables based on common fields. 2. The main types of joins are equijoins, non-equijoins, outer joins, and self joins. 3. Joins allow querying and retrieving data from multiple tables.

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    of 23

    Lecture 9:

    SQL Joins
    Join
    • An SQL JOIN clause is used to combine rows
    from two or more tables, based on a common
    field between them.
    Obtaining Data from Multiple Tables
    DEPARTMENTS
    • EMPLOYEES
    Syntax
    • Use a join to query data from more than one
    table.
    SELECT table1.column, table2.column
    FROM table1, table2
    WHERE table1.column1 = table2.column2
    • Write the join condition in the WHERE clause.
    • Prefix the column name with the table name
    when the same column name appears in more
    than one table.
    Types of join
    • Equijoin
    • Non-equijoin
    • Outer join
    • Self join
    Equijoin
    • SQL EQUI JOIN performs a JOIN against
    equality or matching column(s) values of the
    associated tables. An equal sign (=) is used as
    comparison operator in the where clause to
    refer equality.
    • EQUI JOIN may also be performed by using
    JOIN keyword followed by ON keyword and
    then specifying names of the columns along
    with their associated tables to check equality.
    Example
    EMPLOYEE DEPARTMENT

    Foreign key Primary key


    Syntax
    • 1. SELECT column list
    FROM table1,table2…….
    where
    table1.column_name=table2.column_name
    2. SELECT *
    FROM table1 JOIN table2……
    [ON (join_condition)]
    • 1. SELECT EMP.ENAME, EMP.DEPTNO,
    DEPT.DNAME,DEPT.LOC FROM EMP,DEPT
    WHERE EMP.DEPTNO=DEPT.DEPTNO
    2. SELECT EMP.ENAME, EMP.DEPTNO,
    DEPT.DNAME,DEPT.LOC FROM EMP join
    DEPT
    on EMP.DEPTNO=DEPT.DEPTNO
    Using Table Aliases
    • Simplify queries by using table aliases.
    • Improve performance by using table prefixes.
    Syntax:
    SELECT e.ename,e.deptno, d.loc
    FROM emp e , dept d
    WHERE e.deptno = d.deptno
    Non equijoin
    The SQL NON EQUI JOIN uses comparison operator
    instead of the equal sign like >, <, >=, <= along with
    conditions.
    EMPLOYEES JOB_GRADES

    Salary in the EMPLOYEES table


    must be between lowest
    salary and highest salary in
    the JOB_GRADES table.
    Example
    SELECT e.last_name, e.salary, j.grade_level
    FROM employees e, job_grades j
    WHERE e.salary
    BETWEEN j.lowest_sal AND j.highest_sal
    self join
    • A self join is a join in which a table is joined
    with itself (which is also called Unary
    relationships). To join a table itself means that
    each row of the table is combined with itself
    and with every other row of the table.
    • The self join can be viewed as a join of two
    copies of the same table. The table is not
    actually copied, but SQL performs the
    command as though it were.
    Example
    • SELECT worker.ename,worker.empno,
    manager.ename,manager.empno
    FROM emp worker, emp manager
    WHERE worker.empno<manager.empno
    Outer join
    • The OUTER JOIN clause returns rows even
    when there are no matches between the
    joined tables. It can be useful when there is a
    need to merge data from two tables and to
    include all rows from both tables without
    depending on a match. Another use is to
    generate a large result set for testing
    purposes.
    Example

    DEPARTMENT EMPLOYEE

    There are no employees in


    department 190.
    Types of outer join
    • Left outer join
    • Right outer join
    • Full outer join
    Left outer Join
    • Return rows that have matching data in the
    left table, even if there's no matching rows in
    the right table.
    Syntax:
    SELECT *
    FROM emp e LEFT JOIN dept d
    ON e.deptno = d.deptno
    Right outer Join
    • Return rows that have matching data in the
    right table, even if there's no matching rows in
    the left table.
    Syntax:
    SELECT *
    FROM emp e RIGHT JOIN dept d
    ON e.deptno = d.deptno
    Full outer Join
    • Return all rows, even if there's no matching rows
    in the right table.
    Syntax:
    SELECT *
    FROM emp e left JOIN dept d
    ON e.deptno = d.deptno
    UNION ALL
    SELECT *
    FROM emp e RIGHT JOIN dept d
    ON e.deptno = d.deptno
    Joining more than one table
    • Joining three tables is similar to joining two
    tables, but with one slight wrinkle. With a
    two-table join, there are two tables and one
    join type in the from clause, and with a three-
    table join, there are three tables and two join
    types in the from clause.
    Example
    EMPLOYEE
    DEPARTMENT
    LOCATION
    Syntax
    Select column list
    From employee e JOIN department d
    ON e.department_id=d.department_id
    Join location l
    ON d.department_id=l.location_id

    You might also like