SQL LEFT JOIN

SQL LEFT JOIN (SQL LEFT OUTER JOIN) always contains all records of left table (Table A) even of join condition does not find any matching record in right table (Table B).

SQL OUTER JOIN - OUTER JOIN is a join two table involving common attributes from two tables. But tables (Table A) does not require to have a matching value to other table (Table B).

SQL Left Outer Join

Example Table

Considering following SQL left join example, category, product is our example table.

SQL> SELECT * FROM category;
CATEGORY_IDCATEGORY_NAME
1Mobiles
2Laptops
3Laptops
4Cameras
5Gaming
SQL> SELECT * FROM product;
CATEGORY_IDPRODUCT_NAME
1Nokia
1Samsung
2HP
2Dell
3Apple
4Nikon
NullPlaystation

Run it...   »

Example

SQL> SELECT *
    FROM product LEFT OUTER JOIN category
    ON product.category_id = category.category_id;

CATEGORY_ID PRODUCT_NAME           CATEGORY_ID CATEGORY_NAME
----------- ---------------------- ----------- ----------------------
          1 Samsung                          1 Mobiles
          1 Nokia                            1 Mobiles
          2 Dell                             2 Laptops
          2 HP                               2 Laptops
          3 Apple                            3 Tablet
          4 Nikon                            4 Cameras
       NULL Playstation                   NULL NULL

7 rows selected.

Run it...   »


Following is alternative syntax result produce same as above,

Example

SQL> SELECT *
    FROM product, category
    WHERE product.category_id = category.category_id(+);

CATEGORY_ID PRODUCT_NAME           CATEGORY_ID CATEGORY_NAME
----------- ---------------------- ----------- ----------------------
          1 Samsung                          1 Mobiles
          1 Nokia                            1 Mobiles
          2 Dell                             2 Laptops
          2 HP                               2 Laptops
          3 Apple                            3 Tablet
          4 Nikon                            4 Cameras
       NULL Playstation                   NULL NULL

7 rows selected.

Run it...   »