Joins In SQL With Examples
SQL joins are operations that combine rows from two or more tables based on a related column between them. These related columns are typically primary and foreign keys, establishing a connection between different tables. The result of a join is a new table that combines data from the participating tables.
The syntax of the SQL JOIN statement is:
SELECT columns_from_both_tables
FROM table1
JOIN table2
ON table1.column1 = table2.column2
Here,table1 and table2 are the two tables that are to be joined and column1 is the column in table1 that is related to column2 in table2
In the below PDF we discuss about SQL Joins in detail in simple language, Hope this will help in better understanding.
Types of SQL Joins:
1. INNER JOIN:
The INNER JOIN retrieves rows from both tables that have matching values in the specified columns.
Example:
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
2. LEFT JOIN (or LEFT OUTER JOIN):
The LEFT JOIN retrieves all rows from the left table and matching rows from the right table. If there’s no match, NULL values are returned for columns from the right table.
Example:
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
3. RIGHT JOIN (or RIGHT OUTER JOIN):
The RIGHT JOIN is similar to the LEFT JOIN but retrieves all rows from the right table and matching rows from the left table.
Example:
SELECT orders.order_id, orders.order_date, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;
4. FULL JOIN (or FULL OUTER JOIN):
The FULL JOIN retrieves all rows when there is a match in either the left or right table. If there’s no match, NULL values are returned for columns from the table with no match.
Example:
SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;
Related Question
An SQL Join is a clause used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables in a single query
The ON clause is used to specify the condition for joining tables. It defines the columns from each table that are compared to determine the rows to be included in the result set.
Yes, it is possible to have multiple conditions in a JOIN statement by using logical operators such as AND or OR in the ON clause to specify the relationship between columns.
Joins are used when you need to retrieve data from multiple tables that have a relationship, and you want to combine the information based on a common column. They are essential for querying and analyzing data stored in normalized databases.
Relevant
What is SQL Injection? SQL
Types of SQL Keys Structured
SQL INSERT Statement The SQL
ORDER BY in SQL Structured
SQL Clauses In SQL (Structured
SQL SELECT Statement The SQL
SQL Table Structured Query Language
