Advanced Joins and Subqueries in PostgreSQL

This lesson covers advanced joins and subqueries in PostgreSQL, two powerful tools for combining and querying data across multiple tables.

2.1 Types of Joins

• Inner Join
• Left Join (or Left Outer Join)
• Right Join (or Right Outer Join)
• Full Join (or Full Outer Join)
• Cross Join

2.2 Code Examples

Here are examples of different types of joins:

SELECT a.id, a.name, b.order_id
FROM customers a
INNER JOIN orders b ON a.id = b.customer_id;

SELECT a.id, a.name, b.order_id
FROM customers a
LEFT JOIN orders b ON a.id = b.customer_id;

2.3 Important Notes

3.1 Definition

A subquery is a query nested inside another query. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements.

3.2 Types of Subqueries

• Scalar Subquery
• Row Subquery
• Table Subquery

3.3 Code Examples

Example of a scalar subquery:

SELECT name
FROM customers
WHERE id = (SELECT customer_id FROM orders WHERE order_id = 1);

3.4 Important Notes

• Analyze your queries using EXPLAIN to optimize performance.
• Prefer joins over subqueries for better performance.
• Use indexes on join and filter columns to speed up queries.
• Keep subqueries simple to enhance readability and maintainability.