Joining three or more tables in SQL

Joining three or more tables in SQL can be done using various types of joins such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Here are some examples of how to join three or more tables in SQL:

Example 1: Using INNER JOIN

Suppose we have three tables named "customers", "orders", and "order_details". We want to join these tables to get the customer name, order date, and product name. Here's how we can do it using INNER JOIN:

SELECT customers.customer_name, orders.order_date, order_details.product_name
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_details ON orders.order_id = order_details.order_id;

In this example, we first join the "customers" and "orders" tables on the "customer_id" column. Then we join the resulting table with the "order_details" table on the "order_id" column. The result is a table that contains the customer name, order date, and product name.

Example 2: Using LEFT JOIN

Suppose we have four tables named "employees", "departments", "jobs", and "job_history". We want to join these tables to get the employee name, department name, job title, and start date. Here's how we can do it using LEFT JOIN:

SELECT employees.employee_name, departments.department_name, jobs.job_title, job_history.start_date
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
LEFT JOIN job_history ON employees.employee_id = job_history.employee_id
LEFT JOIN jobs ON job_history.job_id = jobs.job_id;

In this example, we first join the "employees" and "departments" tables on the "department_id" column using LEFT JOIN. Then we join the resulting table with the "job_history" table on the "employee_id" column using LEFT JOIN. Finally, we join the resulting table with the "jobs" table on the "job_id" column using LEFT JOIN. The result is a table that contains the employee name, department name, job title, and start date.

Example 3: Using FULL OUTER JOIN

Suppose we have three tables named "students", "courses", and "enrollments". We want to join these tables to get the student name, course name, and enrollment date. Here's how we can do it using FULL OUTER JOIN:

SELECT students.student_name, courses.course_name, enrollments.enrollment_date
FROM students
FULL OUTER JOIN enrollments ON students.student_id = enrollments.student_id
FULL OUTER JOIN courses ON enrollments.course_id = courses.course_id;

In this example, we first join the "students" and "enrollments" tables on the "student_id" column using FULL OUTER JOIN. Then we join the resulting table with the "courses" table on the "course_id" column using FULL OUTER JOIN. The result is a table that contains the student name, course name, and enrollment date. Note that FULL OUTER JOIN is used here because we want to include all students and all courses, even if they don't have any enrollments.