SQL JOINS (Part -11)

 SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. We use SQL Joins to access data from multiple tables.

There are 6 types of Joins:

1)     INNER JOIN

2)     OUTER JOIN

3)     LEFT OUTER JOIN

4)     RIGHT OUTER JOIN

5)     CROSS JOIN

6)     SELF JOIN

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables.
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

1)     INNER JOIN:

     We can Join 2 tables through the same Column Key. Here, we can match Data by joining 2 tables.

2 )   LEFT OUTER JOIN:

    Here, not only you will get the data in the matching table after joining 2 tables, but also the data present only in the Left table.

3)     RIGHT OUTER JOIN:

           Here we get matching data present in both the tables, but also the data present in Left Outer Join.

4)     FULL OUTER JOIN:

                   Combination of Left and Right Outer Joins s called Full Outer Join.

Here Example:

First Create Database in MySQL:

Use this Database:

Create Table no. 1 inside Database:

Create table Customer_Join(

       Customer_ID int NOT NULL,

       Customer_Name varchar(255) NOT NULL,

       Contact_Name varchar(255),

       Address varchar(255),

      postalcode INT,

      COUNTRY varchar(255),

      PRIMARY KEY (Customer_ID)

       );

Describe table no.1 (Customer_Join):

           Here Primary Key is Customer_ID.

Add Column into the Table:

Insert Data into Tables:

insert into Customer_Join(Customer_ID,Customer_Name,Contact_name,Address,postalcode,COUNTRY,city) values (1,"Krishna","Suri","Mehdipatnam",5042,"India",Hyderabad");

Select * from Table;

Create another Table Order_Join  (table no.2):

Describe table no. 2 (Order_Join);

Here the

     Primary Key in table no. 2 (Order_Join) is Order_ID.

     Foreign Key is table no. 2 (Order_Join) is Customer_ID which is Primary Key in table no.1. 

We use Customer_ID as common Column to Join 2 Tables (1 & 2). 

Insert Data into table no.2 (Order_Join):

Select * from table no.2 (Order_Join):

Select * from table no.1 (Customer_Join):

Join 2 tables to get Matching Data:

       Select * from Customer_Join c INNER JOIN Order_Join o ON c.Customer_ID = o.Customer_ID;

Ommiting repeated & unneccesary tables:

      Select c.customer_ID,c.customer_name,o.order_ID,0.Employee_ID from customer_Join c INNER JOIN Order_Join o ON c.Customer_ID = o.Customer_ID;

We can use just JOIN instead of INNER JOIN:

We can also use “Where” condition also:

LEFT JOIN:

      Select * from customer_Join c LEFT JOIN ORDER_JOIN o ON c.Customer_ID = o.Customer_ID;

RIGHT JOIN similarly:

      Select * from customer_Join c RIGHT JOIN Order_Join o ON c.Customer_ID = o.Customer_ID;

OUTER JOIN:

      In MySQL, FULL OUTER JOIN cannot be possible as it won’t allow.

CROSS JOIN:

Create View from Existing Table: