How to JOIN Tables in SQL

-- Create related tables
CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept_id INTEGER,
  salary REAL
);

INSERT INTO departments VALUES 
  (1, 'Engineering'), (2, 'Marketing'), (3, 'Design');

INSERT INTO employees VALUES 
  (1, 'Alice', 1, 95000),
  (2, 'Bob', 2, 72000),
  (3, 'Charlie', 1, 105000),
  (4, 'Diana', 3, 88000),
  (5, 'Eve', 1, 92000),
  (6, 'Frank', 2, 68000);

-- INNER JOIN
SELECT e.name, d.name as department, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
ORDER BY e.salary DESC;

-- LEFT JOIN (includes unmatched)
SELECT e.name, d.name as department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;