SQL Joins

To merge records from two or more tables in a database, use the SQL Joins clause. A JOIN is a method of joining fields from two tables by using values that are shared by both.

Consider the two tables below. −

CUSTOMERS TABLE (TABLE 1)

EMPLOYEE TABLE TABLE 2

Now, in our SELECT query, let's join these two tables as seen below.

mysql> select customers.cus_id, employee.name, employee.amount from employee

INNER JOIN customers ON employee.amount=customers.salary;

This would result in the following outcome:

It's worth noting that the join is done in the WHERE clause here. Tables can be joined using a variety of operators, including =, >,>, =, >=,!=, BETWEEN, LIKE, and NOT. The equal to symbol, on the other hand, is the most prevalent operator.

In SQL, there are a variety of joins to choose from.

• When both tables have a match, INNER JOIN returns rows.
• Even if there are no matches in the right table, LEFT JOIN returns all rows from the left table.
• Even if there are no matches in the left table, the RIGHT JOIN returns all rows from the right table.
• When there is a match in one of the tables, FULL JOIN returns rows.
• SELF JOIN joins a table to itself as if it were two tables, renaming at least one table in the SQL statement temporarily.
• The Cartesian product of the sets of records from the two or more connected tables is returned by CARTESIAN JOIN.