What are some advanced SQL join examples?

What are some advanced SQL join examples?

Advanced SQL joins include techniques like self joins, cross joins, and using multiple joins in a single query. Would you like to see examples?

Yes, please provide an example of a self join.

Sure! A self join is when a table is joined with itself. For example, if you have an 'employees' table, you can join it to find employees and their managers:

SELECT e1.name AS Employee, e2.name AS Manager 
FROM employees e1 
JOIN employees e2 ON e1.manager_id = e2.id;

That's helpful! Can you explain a cross join?

Absolutely! A cross join returns the Cartesian product of two tables, meaning it combines every row from the first table with every row from the second. For example:

SELECT * 
FROM products 
CROSS JOIN categories;
``` This will give you all combinations of products and categories.

What about using multiple joins in one query?

You can use multiple joins to combine data from several tables. For instance:

SELECT o.id, c.name, p.product_name 
FROM orders o 
JOIN customers c ON o.customer_id = c.id 
JOIN products p ON o.product_id = p.id;
``` This retrieves order details along with customer and product information.