Scribd Logo
Upload

Welcome to Scribd!

  • 6 views

    Joins SQL

    Uploaded by

    kadsjzzzz

    Copyright:

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

    Joins SQL

    Uploaded by

    kadsjzzzz
    6 views16 pages

    Original Title

    joins-sql

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    6 views16 pages

    Joins SQL

    Uploaded by

    kadsjzzzz

    Copyright:

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

    SQL JOINS

    The SQL JOIN statement is used to combine rows from two tables based on a common
    column and selects records that have matching values in these columns.
    SQL JOIN Syntax
    SELECT columns_from_both_tables
    FROM table1
    JOIN table2
    ON table1.column1 = table2.column2
    Here, table1 and table2 are the two tables that are to be joined
    column1 is the column in table1 that is related to column2 in table2
    Example1: join the Customers and Orders tables
    -- based on the common values of their customer_id columns

    SELECT Customers.customer_id, Customers.first_name, Orders.item


    FROM Customers
    JOIN Orders
    ON Customers.customer_id = Orders.customer_id;

    Here, the SQL command joins the Customers and Orders tables based on the common
    column, customer_id of both tables.

    The result set will consist of: customer_id and first_name columns from the Customers table
    item column from the Orders table
    Example2: Join Two Tables Based on Common Column
    -- join Customers and Orders tables based on
    -- customer_id of Customers and customer column of Orders

    SELECT Customers.customer_id, Customers.first_name, Orders.amount


    FROM Customers
    JOIN Orders
    ON Customers.customer_id = Orders.customer;

    Here, the SQL command selects the columns:


    customer_id and first_name from the Customers table, amount from the Orders table
    The result set will contain those values where there is a match between customer_id (of the
    Customers table) and customer (of the Orders table).

    JOIN Multiple Tables


    We can also join more than two tables using JOIN. For example,
    -- join three tables: Customers, Orders, and Shippings

    SELECT Customers.first_name, Orders.item, Shippings.status


    FROM Customers
    JOIN Orders ON Customers.customer_id = Orders.customer_id
    JOIN Shippings ON Customers.customer_id = Shippings.customer;

    This SQL command joins three tables and selects relevant columns from each, based on the
    matching customer_id.

    In SQL, we have four types of joins:

    1. INNER JOIN
    2. LEFT JOIN
    3. RIGHT JOIN
    4. FULL OUTER JOIN

    SQL INNER JOIN


    The SQL INNER JOIN statement joins two tables based on a common column and selects
    rows that have matching values in these columns.
    SQL INNER JOIN Syntax

    SELECT columns_from_both_tables
    FROM table1
    INNER JOIN table2
    ON table1.column1 = table2.column2
    Here,table1 and table2 - two tables that are to be joined,column1 and column2 - columns
    common to in table1 and table2

    Example1:
    -- join Customers and Orders tables with their matching fields customer_id

    SELECT Customers.customer_id, Orders.item


    FROM Customers
    INNER JOIN Orders
    ON Customers.customer_id = Orders.customer_id;

    Here, the SQL command joins the Customers and Orders tables.

    The result includes customer_id (from Customers) and item (from Orders) of rows where
    customer IDs match (Customer.customer_id = Orders.customer_id).
    Example 2:
    -- join the Customers and Orders tables,with customer_id and customer fields

    SELECT Customers.customer_id, Customers.first_name, Orders.amount


    FROM Customers
    INNER JOIN Orders
    ON Customers.customer_id = Orders.customer;
    Here, the SQL command selects the specified rows from both tables if the values of
    customer_id (of the Customers table) and customer (of the Orders table) are a match.
    Example 3: Join Two Tables With a Matching Field
    -- join Categories and Products tables ,with their matching fields cat_id

    SELECT Categories.cat_name, Products.prod_title


    FROM Categories
    INNER JOIN Products
    ON Categories.cat_id = Products.cat_id;
    Here, the SQL command selects common rows between Categories and Products tables with
    the matching field cat_id.

    The result set has the cat_name column from Categories and the prod_title column from
    Products.
    SQL LEFT JOIN Syntax
    SELECT columns_from_both_tables
    FROM table1
    LEFT JOIN table2
    ON table1.column1 = table2.column2
    Here, table1 is the left table to be joined, table2 is the right table to be joined
    column1 and column2 are the common columns in the two tables

    Example1:
    -- left join the Customers and Orders tables

    SELECT Customers.customer_id, Customers.first_name, Orders.amount


    FROM Customers
    LEFT JOIN Orders
    ON Customers.customer_id = Orders.customer;
    RIGHT JOIN SYNTAX
    The syntax of the SQL RIGHT JOIN statement is:

    SELECT columns_from_both_tables
    FROM table1
    RIGHT JOIN table2
    ON table1.column1 = table2.column2
    Here, table1 is the left table to be joined, table2 is the right table to be joined
    column1 and column2 are the related columns in the two tables.
    Example1: SQL RIGHT JOIN
    -- join Customers and Orders tables
    -- based on customer_id of Customers and customer of Orders
    -- Customers is the left table
    -- Orders is the right table

    SELECT Customers.customer_id, Customers.first_name, Orders.amount


    FROM Customers
    RIGHT JOIN Orders
    ON Customers.customer_id = Orders.customer;
    Here, the SQL command selects the customer_id and first_name columns (from the
    Customers table) and the amount column (from the Orders table).

    And, the result set will contain those rows where there is a match between customer_id (of
    the Customers table) and customer (of the Orders table), along with all the remaining rows
    from the Orders table.
    FULL OUTER JOIN SYNTAX
    The syntax of the SQL FULL OUTER JOIN statement is:

    SELECT columns
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column1 = table2.column2;
    Here, table1 and table2 are the tables to be joined, column1 and column2 are the related
    columns in the two tables
    Example1: SQL OUTER Join
    SELECT Customers.customer_id, Customers.first_name, Orders.amount
    FROM Customers
    FULL OUTER JOIN Orders
    ON Customers.customer_id = Orders.customer;
    Here, the SQL command selects the customer_id and first_name columns (from the
    Customers table) and the amount column (from the Orders table).

    The result set will contain all rows of both the tables, regardless of whether there is a match
    between customer_id (of the Customers table) and customer (of the Orders table).
    Create required tables(given sample tables here) and solve below Practice questions:
    The dataset consists of four tables: author, book, adaptation, and book_review.
    AUTHOR table:

    BOOK table:

    ADAPTATION table:
    BOOK_REVIEW Table:

    1. List All Books and Their Authors


    2. List Authors and Books Published After 2005
    3. Show Books Adapted Within 4 Years and Rated Lower Than the Adaptation
    4. display All Books and Their Adaptations (If Any)
    5. Show All Books and Their Movie Adaptations
    6. Show All Books with Their Reviews (If Any)
    7. List All the Books and All the Authors
    8. Show Products Under 150 Calories and Their Department
    9. List All Products with Their Producers, Departments, and Carbs
    10. Show All the Products, Prices, Producers, and Departments
    11. List All Workers and Their Direct Supervisors
    12. Show Cars with Higher Mileage Than a Specific Car
    NOTE: The table data is just for reference , you may populate the tables with more
    rows.
    For Questions 8 to 12 , use below tables
    Department table:

    PRODUCT table:

    NUTITION_DATA table:
    PRODUCER Table:
    SALES_HISTORY Table:

    You might also like