Types of Join

SQL JOIN

• We need tables to implement join query, So, first of all, create table and insert records as per the following commands.
• Example Table:
  Create table category:
  
  CREATE TABLE category(
    category_id number(3) PRIMARY KEY,
    category_name VARCHAR(25)
  );
  
  Insert records into category:
  
  INSERT INTO category VALUES (1,'Mobiles');
  INSERT INTO category VALUES (2,'Laptops');
  INSERT INTO category VALUES (3,'Tablet');
  INSERT INTO category VALUES (4,'Cameras');
  INSERT INTO category VALUES (5,'Gaming');
  
  Create table product:
  
  CREATE TABLE product(
    category_id number(3) REFERENCES category(category_id),
    product_name VARCHAR(25)
  );
  
  Insert records into product:
  
  INSERT INTO product VALUES (1,'Nokia');
  INSERT INTO product VALUES (1,'Samsung');
  INSERT INTO product VALUES (2,'HP');
  INSERT INTO product VALUES (2,'Dell');
  INSERT INTO product VALUES (3,'Apple');
  INSERT INTO product VALUES (4,'Nikon');
  INSERT INTO product VALUES (NULL,PlayStation');

• SQL JOIN four different types: INNER JOIN, OUTER JOIN, SELF JOIN, CROSS JOIN.
• SQL JOIN clause use when select records rows from two or more tables from the database.
•  It depends on certain columns from two tables. 
• Matching columns are evaluated and if predicated TRUE returns a records set data in the specified format.

Inner Join: 

• Inner join check join condition (allow other comparison operators such as <, >, etc) and create recordset results that are combining columns value from the tables(two or more table).
• There are two types:
• Explicit inner join:
• Explicit inner join use INNER JOIN keyword to specify the table to join. 
• And ON keyword to specify join predicates condition. 
• Example:

SQL> SELECT *

    FROM product INNER JOIN category

    ON product.category_id = category.category_id;

• Implicit inner join
• Implicit inner join list of table join using FROM and WHERE clause keyword that are specify the tables and specify join predicates condition. 
• Example:

SQL> SELECT *

    FROM product, category

    WHERE product.category_id = category.category_id;

Equi Join: 

• Equiv join is the same as inner join but different is check condition only specific type comparison (not allowing comparison operator such as <, >, etc).
• Example:

SQL> SELECT *

    FROM product JOIN category

    ON product.category_id = category.category_id;

Natural Join: 

• Natural join is a same as Equi join but different is resulting contains allow only one column for each pair of same columns named. 
• Recordset contains haven't same name columns are found.
• Example:

SQL> SELECT *

    FROM product NATURAL JOIN category;

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 tables (Table B).

Left Join/Left Outer Join: 

• Left Outer Join always contains all records of the left table (Table A) even of the join condition does not find any matching record in the right table (Table B).
• Example-1:

SQL> SELECT *

    FROM product LEFT OUTER JOIN category

    ON product.category_id = category.category_id;

• Example-2:

SQL> SELECT *

    FROM product, category

    WHERE product.category_id = category.category_id(+);

Right Join/Right Outer Join: 

• Right, Outer Join always contains all records of the right table (Table B) even of the join condition does not find any matching record in the left table (Table A).
• Example:

SQL> SELECT * 

    FROM product RIGHT OUTER JOIN category 

    ON product.category_id = category.category_id;

Full Join/Full Outer Join: 

• Full Outer Join always contains all records of left table (Table A) and right table (Table B) even of join condition does not find any matching record in both the left or right table. 
• Returned result contains set NULL value for all columns that are lack of value in matching rows.
• Example:

SQL> SELECT *

    FROM product FULL OUTER JOIN category

    ON product.category_id = category.category_id;

SELF Join: 

• Self Join joining table to itself.
• Create table order with the following records using insert command.

SQL> CREATE TABLE orders(

  order_id number(3),

  product_name VARCHAR(25),

  customer_name VARCHAR(25),

  order_amount number(5)

);

INSERT INTO orders VALUES (1,'Nokia Mobile', 'Opal Kole', 290);

INSERT INTO orders VALUES (2,'Flip Cover', 'Max Miller', 15);

INSERT INTO orders VALUES (3,'Handsfree', 'Beccaa Moss', 7);

INSERT INTO orders VALUES (4,'Canon Camera', 'Paul Singh', 2200);

INSERT INTO orders VALUES (5,'Playstation', 'Max Miller', 70);

INSERT INTO orders VALUES (6,'Sandwich Maker', 'Beccaa Moss', 28);

INSERT INTO orders VALUES (7,'Pen Drive 16GB', 'Opal Kole', 12);

INSERT INTO orders VALUES (8,'Headphone', 'Reed Koch', 38);

INSERT INTO orders VALUES (9,'HP Laptop i5', 'Reed Koch', 1300);

INSERT INTO orders VALUES (10,'Titanic Bluray Disk', 'Beccaa Moss', 78);

• Example:

SQL> SELECT A.order_id, A.product_name, 

             B.order_id, B.product_name, B.customer_name

    FROM orders A 

    INNER JOIN orders B

    ON A.customer_name = B.customer_name

    WHERE A.order_id < B.order_id;

Cross Join: 

• Cross Join joining tables rows and return Cartesian product(each row from Table A with each row of Table B) record set result.
• There are two types:
• Explicit Cross Join:
• Example:

SQL> SELECT * 

    FROM product 

    CROSS JOIN category;

• Implicit Cross Join:
• Example:

SQL> SELECT *

    FROM product, category;