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 -
Consider the Customer table with the following records -
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 −
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
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.
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.
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.
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.