What are the different types of joins in MySQL?

Vaibhav Kothia May 26, 2023 0 Comments

1. Inner Join: An inner join is used to combine rows from two or more tables based on a common field between them. For example, the following query returns all rows from the orders table where the customer_id is present in the customers table:

SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id

2. Left Join: A left join is used to return all rows from the left table, even if there are no matches in the right table. For example, the following query returns all rows from the orders table, even if there are no matching rows in the customers table:

SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id

3. Right Join: A right join is used to return all rows from the right table, even if there are no matches in the left table. For example, the following query returns all rows from the customers table, even if there are no matching rows in the orders table:

SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id

4. Full Join: A full join is used to combine rows from two or more tables, even if there are no matches in either table. For example, the following query returns all rows from both the orders and customers tables, even if there are no matching rows in either table:

SELECT * FROM orders FULL JOIN customers ON orders.customer_id = customers.customer_id

What are the different types of joins in SQL Server?

Vaibhav Kothia May 26, 2023 0 Comments

1. INNER JOIN: This is the most common type of join used in SQL. It returns rows when there is at least one match in both tables.

Example:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column1 = table2.column2;

2. LEFT JOIN: This join returns all rows from the left table, even if there are no matches in the right table.

Example:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2;

3. RIGHT JOIN: This join returns all rows from the right table, even if there are no matches in the left table.

Example:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2;

4. FULL OUTER JOIN: This join returns all rows from both tables, even if there are no matches in either table.

Example:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2;

5. CROSS JOIN: This join returns the Cartesian product of both tables.

Example:

SELECT *
FROM table1
CROSS JOIN table2;