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 will provide clear explanations and examples to help you use PostgreSQL effectively. Let’s dive in!
Scope
This guide help you on how to use SQL joins and conditional statements in PostgreSQL. It covers the basics of different types of joins, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and how to apply conditional statements in queries to filter and retrieve data efficiently.
Purpose
The purpose of this tutorial is to help beginner developers and database administrators on how to:
- Utilize SQL joins and conditional statements in PostgreSQL.
By the end, readers will be able to write queries that combine data from multiple tables and apply conditions to get more accurate results.
Let’s start by setting up our sample tables and data.
Create 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);Usage Instruction
Sample SQL joins and conditional statements:
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:
2. Right Join
A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table. This type of join is useful when you want to include all records from the right table, regardless of whether there is a match in the left table.
Here is an example that retrieve the department name along with the employee’s name (including all departments):
SELECT
d.department_name,
e.name
FROM
departments d
RIGHT JOIN
employees e
ON
d.department_id = e.department_id;
Result:
3. 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:
4. 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:
5. 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:
Final thoughts
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.