Scribd Logo
Upload

Welcome to Scribd!

  • 11 views

    SQL JOINS

    Uploaded by

    hod cse

    Copyright:

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

    SQL JOINS

    Uploaded by

    hod cse
    11 views11 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

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

    SQL JOINS

    Uploaded by

    hod cse

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 11

    SQL JOINS

    • A JOIN clause is used to combine rows from


    two or more relations (known as table), based
    on a matched attributes between relations.
    • Types of join
    – Inner Join
    – Outer Join
    INNER JOINS
    – Result includes only matching rows from the joining
    relations
    – Enable a maximum of 256 tables to be joined
    at the same time.

    – Types of Inner Join


    • Theta join (θ)
    • Equi join
    • Natural join (⋈)
    Student relation Enroll relation
    REGISTER_NO NAME REGISTER_NO COURSE_ID
    1813001 Harry 1813001 C10
    1813002 Steve 1813002 C10
    1813003 Tom 1813002 C20
    1813004 C30

    Select * from Student INNER JOIN Enroll


    on student.register_no = enroll.register_no;

    REGISTER_NO NAME REGISTER_NO COURSE_ID


    1813001 Harry 1813001 C10
    1813002 Steve 1813002 C10
    1813002 Steve 1813002 C20
    Natural Join
    • Natural join operates on two or more relations
    matches tuples on common attributes that are
    common to joining relations.
    • columns with the same name of associated
    tables will appear once only.
    REGISTER_NO NAME COURSE_ID
    1813001 Harry C10
    1813002 Steve C20
    Student relation Enroll relation
    REGISTER_NO NAME REGISTER_NO COURSE_ID
    1813001 Harry 1813001 C10
    1813002 Steve 1813002 C20
    1813003 Tom 1813004 C30

    SELECT * FROM Student


    INNER JOIN Enroll on Student.register_no > Enroll.register_no;

    REGISTER_NO NAME REGISTER_NO COURSE_ID


    1813003 Tom 1813001 C10
    1813003 Tom 1813002 C20
    Outer joins
    – return all matching rows, plus nonmatching rows
    from one or both tables
    – can be performed on only two tables or views
    at a time.
    Left outer join
    • This join returns all the rows from left table
    combine with the matching rows of the right
    table. For no matching rows in the right table
    it returns NULL values.

    Left outer join Full outer join Right outer join


    Left outer join
    • This join returns all the rows from left table
    combine with the matching rows of the right
    table. For no matching rows in the right table
    it returns NULL values.
    Right outer join
    • This join returns all the rows from left table
    combine with the matching rows of the right
    table. For no matching rows in the right table
    it returns NULL values.
    Full outer join
    • This join returns all the rows from left table
    combine with the matching rows of the right
    table. For no matching rows in the right table
    it returns NULL values.

    You might also like