In general meaning join means combined something, In SQL using join keyword you can join two tables. Joins clause is used to combine rows from two or more tables, based on a common field between them.
There are following joins are available in SQL
- Inner Join
- Left Join
- Right Join
- Full Join
When SQL Join is used ?
- When you need to show record of two table combinedly.
- If you want to access more than one table through a select statement.
- If you want to combine two or more table.
- If you want to joint two or more table based on common field between
- To combine rows from two or more tables, based on a common field between them.
- Inner Join: Returns all rows when there is at least one match in both tables.
- Left Join: Return all rows from the left table, and the matched rows from the right table.
- Right Join: Return all rows from the right table, and the matched rows from the left table.
- Full Join: Return all rows when there is a match in ONE of the tables.
Example of Join
Below we combined two table, table employee and table payment.
After apply join on these two table, result show like below;
SELECT emp_id, name, age,salary FROM Employee e, Payment p WHERE e.Emp_id =p.Payment_id;
In above syntax we select 4 columns, emp_id, name, age and salary from table Employee and table Payment. And retrieving all rows where the employee identification number is the same in both the Employee and Payment tables.