In this blog, we will explore some fundamental SQL concepts using sample tables and records within a PostgreSQL database, as established from this article. We’ll cover INNER JOIN, LEFT OUTER JOIN, and the CASE WHEN ELSE END statement. Let’s start by setting up our sample tables and data.
Sample Tables and Data
We will create three tables: employees, departments, and bonuses. Here is the SQL code to create these tables and insert some sample records.
-- Create the employees table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
department_id INT,
salary INT
);
-- Insert records into the employees table
INSERT INTO employees (employee_id, name, department_id, salary) VALUES
(1, 'Alice', 1, 60000),
(2, 'Bob', 2, 45000),
(3, 'Charlie', NULL, 30000),
(4, 'David', 1, 70000);
-- Create the departments table
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50)
);
-- Insert records into the departments table
INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Engineering'),
(3, 'Marketing');
-- Create the bonuses table
CREATE TABLE bonuses (
employee_id INT PRIMARY KEY,
bonus_amount INT
);
-- Insert records into the bonuses table
INSERT INTO bonuses (employee_id, bonus_amount) VALUES
(1, 5000),
(2, 3000),
(4, 7000);Step-by-Step Guide: Example SQL Queries
Step 1. INNER JOIN
An INNER JOIN returns only the rows where there is a match in both tables. Here is an example that retrieves the employee’s name and their department name.
SELECT
e.name AS EmployeeName,
d.department_name AS DepartmentName
FROM
employees e
INNER JOIN
departments d
ON
e.department_id = d.department_id;
Result:
Step 2. LEFT OUTER JOIN
A LEFT OUTER JOIN returns all the rows from the left table, and the matched rows from the right table. Here is an example that retrieves all employees, their departments, and includes employees without departments.
SELECT
e.name AS EmployeeName,
d.department_name AS DepartmentName
FROM
employees e
LEFT OUTER JOIN
departments d
ON
e.department_id = d.department_id;
Result:
Step 3. CASE WHEN ELSE END
The CASE statement allows you to add conditional logic in your SQL queries. Here is an example that categorizes employees based on their salary.
SELECT
name,
salary,
CASE
WHEN salary > 50000 THEN 'High'
WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM
employees;
Results:
Step 4. Combined Example with Results
Let’s combine the concepts of JOIN and CASE. We will retrieve the employee’s name, their department name, and categorize their salary.
SELECT
e.name AS EmployeeName,
d.department_name AS DepartmentName,
e.salary,
CASE
WHEN e.salary > 50000 THEN 'High'
WHEN e.salary BETWEEN 30000 AND 50000 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM
employees e
LEFT OUTER JOIN
departments d
ON
e.department_id = d.department_id;Result:
With these examples, you should have a good foundation for working with INNER JOIN, LEFT OUTER JOIN, and CASE WHEN ELSE END in SQL. Practice these queries with your own data to become more comfortable with these essential SQL operations.