SQL Join

The SQL join statement is used to join the data or records of two or more tables based on the corresponding column between them.There are four different types of SQL joins: inner, left, right, and full.The easiest and most common way to learn the difference between Sql joins is to use the Venn diagram.

SQL Join Example :

Consider the Product table with the following records -

ProductID CustomerID ProductCost ProductName
1000 2 500 Keyboards
1001 4 100 USB
1002 1 1500 Bags


Consider the Customer table with the following records -

CustomerID CustomerName Age Address CustomerSalary
1 Aarav 36 Udaipur 35000
2 Vivaan 33 Mumbai 30000
3 Reyansh 28 Chennai 40000
4 Muhammad 29 Udaipur 50000
5 Sai 27 Mumbai 27000


TEST CASE 1 :- The "CustomerID" column in the "Product" table refers to the "CustomerID" in the "Customer" table. The relationship between the two tables above is the "CustomerID" column.
We can create the following SQL statement (containing an INNER JOIN), which selects the records that have matching values ​​in both tables.

SELECT Product.ProductID, Customer.CustomerName, Product.ProductName
FROM Product
INNER JOIN Customer ON Product.CustomerID=Customer.CustomerID;
   


The result for the respective sql query is as follows −

ProductID CustomerName ProductName
1000 Vivaan Keyboards
1001 Muhammad USB
1002 Aarav Bags



Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

INNER JOIN

Select all records from Table A and Table B, where the join condition is met. SQL inner joins are used to join data or records of two or more tables based on matching values ​​in both tables. The Inner Join will create the resulting set until the condition is satisfied by combining all rows from both tables.

LEFT (OUTER) JOIN

SQL Left Join is used to return all data or records from the Table 1 plus match records to tables 2. If there is no match, the result from the right will be NULL.

RIGHT (OUTER) JOIN

SQL Right Join is used to return all data or records from the Table 2 plus match records to tables 1. If there is no match, the result from the right will be NULL.

FULL (OUTER) JOIN

SQL Full Join is used to return all data or records when a match occurs in a Table 1 or Table 2 record and NULLs have to be filled in for missing matches on either side. It is also known as Full (OUTER) JOIN.





Visit :


Discussion


* You must be logged in to add comment.