Web | This Site

Learning Basic SQL
Advanced SQL
SQL Functions
SQL Useful Resources
Selected Reading
© 2011 TutorialsPoint.COM

| Home |  | References |  | Discussion Forums |  | About TP

# SQL - RIGHT JOINS

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result.but with NULL in each column from left table.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

# Syntax:

The basic syntax of RIGHT JOIN is as follows:

    SELECT table1.column1, table2.column2...
    FROM table1
    RIGHT JOIN table2
    ON table1.common_filed = table2.common_field;

# Example:

Consider following two tables, (a) CUSTOMERS table is as follows:

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY   |
    +----+----------+-----+-----------+----------+
    |  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
    |  2 | Khilan   |  25 | Delhi     |  1500.00 |
    |  3 | kaushik  |  23 | Kota      |  2000.00 |
    |  4 | Chaitali |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik   |  27 | Bhopal    |  8500.00 |
    |  6 | Komal    |  22 | MP        |  4500.00 |
    |  7 | Muffy    |  24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+

(b) Another table is ORDERS as follows:

    +-----+---------------------+-------------+--------+
    | OID | DATE                |          ID | AMOUNT |
    +-----+---------------------+-------------+--------+
    | 102 | 2009-10-08 00:00:00 |           3 |   3000 |
    | 100 | 2009-10-08 00:00:00 |           3 |   1500 |
    | 101 | 2009-11-20 00:00:00 |           2 |   1560 |
    | 103 | 2008-05-20 00:00:00 |           4 |   2060 |
    +-----+---------------------+-------------+--------+

Now let us join these two tables using RIGHT JOIN as follows:

    SQL> SELECT  ID, NAME, AMOUNT, DATE
         FROM CUSTOMERS
         RIGHT JOIN ORDERS
         ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce following result:

    +------+---------+--------+---------------------+
    | ID   | NAME    | AMOUNT | DATE                |
    +------+---------+--------+---------------------+
    |    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
    |    3 | kaushik |   1500 | 2009-10-08 00:00:00 |
    |    2 | Ramesh  |   1560 | 2009-11-20 00:00:00 |
    |    4 | kaushik |   2060 | 2008-05-20 00:00:00 |
    +------+---------+--------+---------------------+

Advertisement

Online Image Processing

Indian Baby Names