In a relational database, SQL join is a very important feature. SQL Joins are used to get data from other tables. It is a part of SQL query that retrieves rows from two or more tables. This article describes about SQL joins and what are the types of join in SQL. Summary of the article:
- What is Join?
- Types of Join
- What is Inner join?
- What is Outer Join?
- What is Cross Join?
- What is Self Join?
What is Join?
Every relational database used normalization to remove data duplication. That’s why multiple tables are used to store data. The SQL join is a mechanism to combined two or more tables and get data from them. It is a basic feature of any relational database.
By using joins, We can retrieve data from two or more tables based on logical relationships between the tables. It is used in the SQL WHERE clause of select, update, delete statements.
Types of Join
SQL join is mainly three types:
- Inner join
- Outer Join
- Cross Join
What is Inner join?
Inner join returns only the common data between two or more tables.
What is Outer Join?
Outer join is three types:
- Left Join or Left Outer Join
Left join returns all data from the left table. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
Right Join or Right Outer Join
Right join returns all data from right table. When a row in the right table has no matching rows in the left table, the associated result set row contains null values for all select list columns coming from the left table.
Full Join or Full Outer Join
Full joins returns all rows in both the left and right table. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
What is Cross Join?
Cross join returns all the rows from the left table. Every row from the left table is combined with all rows from the right table. It is also called Cartesian products.
What is Self Join?
In a self-join a table can be joined to itself. When we need to join a table with it, we use self-join. It is a special type of join.
That’s all about SQL Join.