You are currently viewing SQL 1.25 SQL LEFT JOIN 

SQL 1.25 SQL LEFT JOIN 


Introduction to SQL LEFT JOIN

SQL LEFT JOIN is a powerful tool used to combine rows from two or more tables based on a related column between them. The LEFT JOIN keyword returns all rows from the left table (table1), along with matching rows from the right table (table2). If there is no match, NULL values are returned for the columns from the right table.

In this tutorial, we’ll cover the basics of SQL LEFT JOIN with detailed explanations and examples.

Prerequisites:

  • Basic understanding of SQL syntax.
  • Familiarity with creating and querying tables.

1. Syntax of SQL LEFT JOIN:

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
  • SELECT: Specifies the columns you want to retrieve from the result set.
  • FROM: Specifies the left table.
  • LEFT JOIN: Joins the left table with the right table based on the specified condition.
  • table1.column = table2.column: Specifies the condition for joining the tables.

2. Example Scenario:

Let’s consider two tables: students and grades.

Table: students

student_idname
1Alice
2Bob
3Charlie

Table: grades

student_idsubjectgrade
1MathA
2ScienceB
4EnglishA

In this scenario, we want to retrieve all students along with their grades (if available).

3. Sample SQL Query with LEFT JOIN:

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades ON students.student_id = grades.student_id;

4. Explanation:

  • SELECT students.name, grades.subject, grades.grade: We specify the columns we want to retrieve. We’re interested in the student’s name, the subject they’re graded in, and their grade.
  • FROM students: We specify the left table as students.
  • LEFT JOIN grades: We join the students table with the grades table using LEFT JOIN.
  • ON students.student_id = grades.student_id: We specify the condition for the join, which is matching student_id in both tables.

5. Output:

namesubjectgrade
AliceMathA
BobScienceB
CharlieNULLNULL

6. Interpretation:

  • Alice and Bob have grades recorded in the grades table, so their information is displayed along with their respective grades.
  • Charlie doesn’t have any corresponding record in the grades table, so NULL values are displayed for subject and grade.

7. Additional Notes:

  • The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.
  • If there are multiple matching rows in the right table, the LEFT JOIN will return all combinations.
  • Using aliases for table names (students AS s, grades AS g) can improve readability, especially in complex queries.

Conclusion

Congratulations! You’ve now mastered the basics of SQL LEFT JOIN. You understand its syntax, how it works, and how to use it in practical scenarios. Keep practicing and exploring more advanced SQL concepts to enhance your skills further.

Leave a Reply