SQL is the special-purpose programming language for managing information in the relational database management system (RDBMS). The word relational is key; it specifies that the DBMS is organized to define clear relations between the different data sets.
SQL JOIN
SQL JOIN combines the records from two or more tables in a database. An SQL JOIN clause combines the rows from two or more tables based on a related column.
Types of Joins
INNER JOIN in SQL
SQL Inner Join is used to select all the rows from tables for the match between the columns in tables. SQL INNER JOIN is based on the concept of EQUI JOINS.
EQUI JOINS are those who use the comparison operator (=) for combining records from two or more tables.
When the condition is satisfied, column values for each matched pair of rows of two tables are combined into a result row.
The shaded part above Shows common records between both the tables.
Syntax
Select columns from Table_1 INNER JOIN Table_2 on Table_1.column = Table_2.column;
So, in the above statements, columns represent the column names of the tables.
Table_1 and Table_2 are the names of the tables.
And the condition i.e Table_1.column = Table_2.column is used to compare the columns which are common in both the tables.
Table_1: Employee
| Emp_id | Emp_name | City | State | Salary |
| 101 | Rohit | Patna | Bihar | 30000 |
| 201 | Shivam | Jalandhar | Punjab | 20000 |
| 301 | Karan | Allahabad | Uttar Pradesh | 40000 |
| 401 | Suraj | Kolkata | West Bengal | 60000 |
| 501 | Akash | Vizag | Andhra Pradesh | 70000 |
Table_2: Department
| Dept_no | Emp_id |
| 123 | 301 |
| 214 | 401 |
| 125 | 505 |
See the following query.
Select Department.Dept_no, Employee.Emp_name, Employee.City, Employee.Salary from Department INNER JOIN Employee on Department.Emp_id = Employee.Emp_id;
Output
LEFT JOIN in SQL
A left outer join returns all rows in the table, which is on the left side, matched with the rows of a table on the right side. This concludes that the SQL left Join always contains the rows in the left table.
The above Venn diagram shows that the left table rows will always be displayed whether the conditions match.
Syntax
Select column_1, column_2… from table_1 LEFT JOIN table_2 ON CONDITION;
See the following tables.
CUSTOMER:
| ID | NAME | AGE | CITY |
| 1 | Rohit | 20 | Patna |
| 2 | Shivam | 18 | Jalandhar |
| 3 | Pranav | 19 | Dharamshala |
ORDERS:
| O_ID | Cust_ID | City | AMOUNT |
| 201 | 1 | Patna | 3000 |
| 202 | 2 | Jalandhar | 4000 |
| 203 | 4 | Kolkata | 1000 |
Let’s clear this with an example.
Select Orders.O_ID, Customer.id, customer.name, Orders.amount From CUSTOMER LEFT JOIN Orders ON Customer.ID = Orders.Cust_ID;
See the following output.
As you can see, all the contents of a left table are displayed, whether it is matched with the right table or not.
Right table contents that are matched with the left table are displayed as well, and those that are not matched are displayed with NULL values.
RIGHT JOIN in SQL
The SQL right join returns all the values from the rows of the right table. The right join includes matched values from a left table, but if there is no matching in both tables, it returns the NULL values.
The above Venn diagram illustrates that all the rows of the right table will be displayed whether the condition matches.
Syntax
Select column_1, column_2… from table_1 RIGHT JOIN table_2 ON CONDITION;
CUSTOMER:
| ID | NAME | AGE | CITY |
| 1 | Rohit | 20 | Patna |
| 2 | Shivam | 18 | Jalandhar |
| 3 | Pranav | 19 | Dharamshala |
ORDERS:
| O_ID | Cust_ID | City | AMOUNT |
| 201 | 1 | Patna | 3000 |
| 202 | 2 | Jalandhar | 4000 |
| 203 | 4 | Kolkata | 1000 |
Query
Select Orders.O_ID,Customer.id,customer.name,Orders.amount From CUSTOMER RIGHT JOIN Orders ON Customer.ID = Orders.Cust_ID;
OUTPUT
EXPLANATION
As you can see, all the contents of the right table are displayed, whether it is matched with a left table or not.
Left table contents that are matched with a right table are displayed as well, and those that are not matched are displayed with the NULL values.
FULL JOIN in SQL
SQL full join returns all the rows in the left table, right table, and matching rows in both the tables, or you can say it is a combination of left and right join. It is also known as a full outer join.
The above Venn diagram illustrates that all the rows of the table will be displayed whether the conditions match.
Syntax
Select * from table1 FULL OUTER JOIN table2 ON CONDITION;
Let’s consider a table.
CUSTOMER:
| ID | NAME | AGE | CITY |
| 1 | Rohit | 20 | Patna |
| 2 | Shivam | 18 | Jalandhar |
| 3 | Pranav | 19 | Dharamshala |
ORDERS:
| O_ID | Cust_ID | City | AMOUNT |
| 201 | 1 | Patna | 3000 |
| 202 | 2 | Jalandhar | 4000 |
| 203 | 4 | Kolkata | 1000 |
Let’s clear this with an example.
Select * from Customer FULL OUTER JOIN Orders ON Customer.ID = Orders.cust_id;
OUTPUT
The above Statement will not work in MySQL, Because the SQL full outer join returns the result set that is the combined results of both SQL left join and SQL right join. So to generate the result, we have to use the UNION operator.
Statement
Select * from Customer LEFT JOIN Orders ON Customer.id = Orders.Cust_ID UNION Select * from Customer RIGHT JOIN Orders ON Customer.id = Orders.Cust_id;
See the output.
CROSS JOIN in SQL
SQL Cross Join is used to join the table having no condition in which all the records of the first table come with all the records of the second table.
This type of join is also called a Cartesian product.
Unlike the INNER JOIN or LEFT JOIN, the cross join does not establish a relationship between the joined tables.
NOTE:
If Where Condition is not used with CROSS JOIN, it will behave like a cartesian product.
Here arrows are pointing to the rows of a table.
Syntax
SELECT COLUMNS_NAME FROM TABLE_1 CROSS JOIN TABLE_2;
Suppose there are two tables.
QUERY
Select * from STUDENT CROSS JOIN COURSE;
OUTPUT
All the combinations of rows are listed.
SELF JOIN in SQL
Self-join joins the table itself. For example, each row is joined with itself and all other rows depending on the conditions.
Syntax
SELECT a.column1, b.column2 from table_name a, table_name b where condition;
Table: (Employee)
| Emp_id | Emp_name | City | State | Salary |
| 101 | Rohit | Patna | Bihar | 30000 |
| 201 | Shivam | Jalandhar | Punjab | 20000 |
| 301 | Karan | Allahabad | Uttar Pradesh | 40000 |
| 401 | Suraj | Kolkata | West Bengal | 60000 |
| 501 | Akash | Vizag | Andhra Pradesh | 70000 |
QUERY
Select a.emp_name,b.salary from employee a,employee b where a.salary < b.salary;
OUTPUT
That’s it for this tutorial.
