ГДЕ против ИМЕЮЩИХ?

SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE hire_date > '2020-01-01'      -- Filter rows first
GROUP BY department
HAVING AVG(salary) > 60000;         -- Then filter groups

┌──────────────────────────────────────────────────────────────────────────────┐
│                         THE JOIN FAMILY TREE                                 │
├──────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   INNER JOIN                    LEFT JOIN                    RIGHT JOIN      │
│                                                                              │
│      A       B                    A       B                    A       B     │
│    ┌───┐   ┌───┐                ┌───┐   ┌───┐                ┌───┐   ┌───┐   │
│    │███│   │███│                │███│███│   │                │   │███│███│   │
│    │███│███│███│                │███│███│   │                │   │███│███│   │
│    │███│   │███│                │███│███│   │                │   │███│███│   │
│    └───┘   └───┘                └───┘   └───┘                └───┘   └───┘   │
│      ▓▓▓ Only matching           ▓▓▓ All from A              ▓▓▓ All from B  │
│                                                                              │
│   FULL OUTER JOIN              CROSS JOIN                   SELF JOIN        │
│                                                                              │
│      A       B                    A       B                    A ←→ A        │
│    ┌───┐   ┌───┐                ┌───┐   ┌───┐                                │
│    │███│███│   │                │███│███│███│                Employee        │
│    │███│███│███│                │███│███│███│                ├─ id           │
│    │   │███│███│                │███│███│███│                ├─ name         │
│    └───┘   └───┘                └───┘   └───┘                └─ manager_id   │
│      ▓▓▓ All from both           ▓▓▓ Every row x                             │
│                                  every row (Cartesian)       Join to itself  │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘

-- Sample tables
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2)
);

SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;
-- Only customers who HAVE placed orders

SELECT c.name, COALESCE(o.amount, 0) AS amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- ALL customers, even those who never ordered (amount shows 0)

-- PostgreSQL
SELECT * FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

-- MySQL workaround with UNION
SELECT * FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT * FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;

-- Every color paired with every size
SELECT color, size FROM colors CROSS JOIN sizes;  -- 5 colors × 4 sizes = 20 rows

-- Find employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- UNION: Combine, remove duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL: Combine, keep duplicates (FASTER — use when you know there are no dupes)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- INTERSECT: Only rows present in BOTH (not in MySQL)
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

-- EXCEPT / MINUS: In first set but NOT in second
SELECT city FROM customers
EXCEPT           -- PostgreSQL / SQL Server
-- MINUS        -- Oracle
SELECT city FROM suppliers;