how to join two tables in sql

To join two tables in SQL, you use the JOIN clause, which combines rows from two or more tables based on a related column between them. The most common types of joins include:

• INNER JOIN : Returns only rows where there is a match in both tables.
• LEFT JOIN (or LEFT OUTER JOIN) : Returns all rows from the left table and matched rows from the right table; unmatched rows from the right table will have NULLs.
• RIGHT JOIN (or RIGHT OUTER JOIN) : Returns all rows from the right table and matched rows from the left table; unmatched rows from the left table will have NULLs.
• FULL JOIN (or FULL OUTER JOIN) : Returns rows when there is a match in one of the tables; unmatched rows will have NULLs in the columns of the table without a match.
• CROSS JOIN : Returns the Cartesian product of rows from the tables (every row from the first table combined with every row from the second).

Basic Syntax for Joining Two Tables

sql

SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;

• table1 and table2 are the tables you want to join.
• common_column is the column that exists in both tables and is used to match rows.
• You can specify the type of join by replacing JOIN with INNER JOIN, LEFT JOIN, etc.

Example: Inner Join

sql

SELECT *
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

This query returns rows where DepartmentID matches in both Employees and Departments tables

Using Aliases and Selecting All Columns

You can select all columns from both tables using * with table aliases:

sql

SELECT e.*, d.*
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

This retrieves all fields from both tables where the join condition is met

Using USING Clause

If the column name is the same in both tables, you can use:

sql

SELECT *
FROM table1
JOIN table2
USING (common_column);

This simplifies the syntax by avoiding the need to specify the table name for the join column

Summary

• Use JOIN with ON to specify the join condition.
• Use INNER JOIN to get matching rows from both tables.
• Use LEFT JOIN or RIGHT JOIN to include all rows from one table and matched rows from the other.
• Use * or table_alias.* to select all columns from one or both tables.
• Use USING if the join column name is the same in both tables.

These techniques allow you to combine data from two tables effectively in SQL