SQL Joins (INNER, LEFT, RIGHT, FULL JOIN)
SQL Joins (INNER, LEFT, RIGHT, FULL JOIN)
SQL Joins are used to combine data from two or more tables based on a related column. Joins are essential in relational databases because data is often stored in multiple tables, and combining them helps in retrieving meaningful information.
In this lesson, you will learn different types of SQL joins and how they are used in real-world database queries.
What are SQL Joins?
SQL joins allow you to retrieve data from multiple tables using a common field, usually a primary key and a foreign key.
SQL joins are widely used in databases like MySQL, PostgreSQL, and Microsoft SQL Server.
Types of SQL Joins
1. INNER JOIN
INNER JOIN returns only the records that have matching values in both tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Example:
SELECT Students.Name, Courses.Course_Name
FROM Students
INNER JOIN Courses
ON Students.Course_ID = Courses.Course_ID;
2. LEFT JOIN
LEFT JOIN returns all records from the left table and matched records from the right table. If no match is found, NULL values are returned.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
3. RIGHT JOIN
RIGHT JOIN returns all records from the right table and matched records from the left table.
4. FULL JOIN
FULL JOIN returns all records when there is a match in either table. If there is no match, NULL values are returned.
Note: Some databases like MySQL do not directly support FULL JOIN, but it can be achieved using UNION.
Real-World Example
Tables: Students and Courses
- Students table contains student details
- Courses table contains course details
Using joins:
- INNER JOIN → Students enrolled in courses
- LEFT JOIN → All students, even if not enrolled
- RIGHT JOIN → All courses, even if no students
Why SQL Joins are Important
SQL joins help you:
- Combine data from multiple tables
- Perform complex queries
- Build real-world applications
- Improve database efficiency
FAQs
What is SQL join?
SQL join is used to combine data from two or more tables based on a related column.
What is INNER JOIN?
INNER JOIN returns only matching records from both tables.
What is LEFT JOIN?
LEFT JOIN returns all records from the left table and matched records from the right table.
Does MySQL support FULL JOIN?
No, MySQL does not directly support FULL JOIN but it can be implemented using UNION.
Where can I learn more courses like this?
Click here for more free courses