Scribd Logo
Upload

Welcome to Scribd!

  • 187 views

    MySQL Joins

    Uploaded by

    qwerrty
    This document discusses various types of joins in MySQL including inner joins, equi joins, natural joins, cross joins, left outer joins, right outer joins, and self joins. It provides syntax examples for each type of join and explains how they combine records from two or more tables based on common columns.

    Copyright:

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

    MySQL Joins

    Uploaded by

    qwerrty
    187 views14 pages
    This document discusses various types of joins in MySQL including inner joins, equi joins, natural joins, cross joins, left outer joins, right outer joins, and self joins. It provides syntax examples for each type of join and explains how they combine records from two or more tables based on common columns.

    Original Description:

    MySQL Joins

    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 various types of joins in MySQL including inner joins, equi joins, natural joins, cross joins, left outer joins, right outer joins, and self joins. It provides syntax examples for each type of join and explains how they combine records from two or more tables based on common columns.

    Copyright:

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

    MySQL Joins

    Uploaded by

    qwerrty
    This document discusses various types of joins in MySQL including inner joins, equi joins, natural joins, cross joins, left outer joins, right outer joins, and self joins. It provides syntax examples for each type of join and explains how they combine records from two or more tables based on common columns.

    Copyright:

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

    Building Result Set From Multiple Tables

    MySQL Joins
    MySQL Joins Overview
    • MySQL Join is used to join the records from two table using
    join clause.
    • The Join Clause return you the set of records from both table
    on the basis of common column.
    • The Join return you only match able records from both table
    on the basis of where clause .
    MySQL Join Types

    • MySQL Inner Join


    • MySQL Equi Join
    • MySQL Natural Join
    • MySQL Cross Join
    • MySQL Outer Join
    • Left Outer Join
    • Right Outer Join
    • Self Join
    MySQL Join Types
    MySQL Inner Join
    • Mysql Inner Join returns the set of only those records
    which matches in one table with another.

    • mysql> select
    emp.firstname,emp.city,peson.firstname,person.email
    from emp,person where emp.empid=person.empid;
    MySQL Equi Join
    • Equi Join is a classified type of Inner Join in Mysql.
    • Equi Join is used to combine records from two table based on
    the common column exists in both table.
    • The Equi Join returns you only those records which are
    available in both table on the basis of common primary field
    name.
    • mysql> select emp.firstname,emp.city,contact.phone from
    employee,contact where emp.empid=contact.empid;
    MySQL Natural Join
    • MySQL Natural Join is a specialization of equi-joins.
    • The join compares all columns in both tables that
    have the same column-name in both tables that have
    column name in the joined table.

    • mysql> SELECT e.FirstName, e.city,e.empid FROM


    employee as e NATURAL JOIN contact as c;
    MySQL Cross Join
    • Cross Join is also called Cartesian Product Join.
    • The Cross Join in SQL return you a result table in
    which each row from the first table is combined with
    each rows from the second table.
    • In other words, you can say it is the cross
    multiplication of number of rows in each table.

    • select * from country cross join province


    MySQL Outer Join
    • MySQL Outer Join return you the set of all matching
    records from both table.
    • The Outer Join does not requires each records to be
    matched in both the tables.

    • MySQL Outer Join is categorized into two groups.


    • MySQL Left Outer Join
    • MySQL Right Outer Join
    MySQL Left Outer Join
    • The left join is used in case of need to return all rows
    from the left table, even if the right table doesn't
    have any match.
    • mysql> SELECT e.FirstName, e.city,e.empid FROM
    employee as e LEFT JOIN contact as c
    ON e.empid=c.empid;
    MySQL Right Outer Join
    • The right join is used in case of need to return all
    rows from the right table, even if the left table
    doesn't have any match.
    • mysql> SELECT e.FirstName, e.city,e.empid FROM
    employee as e RIGHT JOIN contact as c
    ON e.empid=c.empid;
    MySQL Self Join
    • These join allow you to retrieve related records from
    the same table.
    • The most common case where you'd use a self-join is
    when you have a table that references itself.

    • Syntax:
    • SELECT a.column_name, b.column_name... FROM table1 a,
    table1 b WHERE a.common_filed = b.common_field;
    MySQL Self Join
    • id first_ name last_name manager
    --- --------------- --------------- ---------------
    • 1 Pat Crystal NULL
    2 Dennis Miller 1
    3 Jacob Smith 1
    4 Allen Hunter 2
    5 Mary Underwood 3
    6 Joy Needham 3
    MySQL Self Join
    • In this table, the manager attribute simply references
    the employee ID of another employee in the same
    table.

    • SELECT e.first_name AS 'Employee FN', e.last_name


    AS 'Employee LN', m.first_name AS 'Manager FN',
    m.last_name AS 'Manager LN'
    FROM employees AS e LEFT OUTER JOIN employees
    AS m
    ON e.manager =m.id

    You might also like