SQL joins(1)

SQL Join Syntax and Application

Common Syntax for SQL Joins

1. INNER JOIN Syntax:

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

Example of INNER JOIN:

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE  
FROM Student  
INNER JOIN StudentCourse ON Student.ROLL_NO = StudentCourse.ROLL_NO;

2. LEFT JOIN Syntax:

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

Example of LEFT JOIN:

SELECT Student.NAME, StudentCourse.COURSE_ID  
FROM Student  
LEFT JOIN StudentCourse ON StudentCourse.ROLL_NO = Student.ROLL_NO;

3. RIGHT JOIN Syntax:

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

Example of RIGHT JOIN:

SELECT Student.NAME, StudentCourse.COURSE_ID  
FROM Student  
RIGHT JOIN StudentCourse ON StudentCourse.ROLL_NO = Student.ROLL_NO;

4. CROSS JOIN Syntax:

SELECT * FROM table1 CROSS JOIN table2;

Example of CROSS JOIN:

SELECT * FROM CUSTOMER CROSS JOIN ORDERS;

Summary of Applications

  • INNER JOIN: Use when you need to retrieve records that have matching values in both tables.

  • LEFT JOIN: Useful for retrieving all records from the left table even when there are no matches in the right table.

  • RIGHT JOIN: Utilize this when you want all records from the right table with matching records from the left table.

  • CROSS JOIN: Ideal for scenarios where every combination of rows from both tables is required, such as combinations of products and discounts.