SQL JOINs Part 2 in PostgreSQL: Complete Guide to RIGHT JOIN, FULL OUTER JOIN, SELF JOIN, CROSS JOIN and Advanced SQL Queries
Introduction
In the previous SQL lessons, we learned how relational databases store information in multiple tables and how SQL JOINs help us retrieve related information from those tables. We explored INNER JOIN and LEFT JOIN and understood how two tables can be connected using primary keys and foreign keys.
But real-world databases are not always limited to two tables. An e-commerce application may contain customers, orders, products, payments, employees, categories, and many other related tables. To retrieve meaningful information from these tables, we need to understand more advanced JOIN operations.
In this lesson, we will explore advanced SQL JOIN concepts using PostgreSQL. We will understand how RIGHT JOIN and FULL OUTER JOIN work, how to join a table with itself using SELF JOIN, and how CROSS JOIN generates every possible combination of records. We will also learn how to join three or more tables and combine JOIN with WHERE, GROUP BY, HAVING, and ORDER BY.
By the end of this tutorial, you will understand how to write more advanced SQL queries and use JOINs to solve practical database problems.
1. Understanding RIGHT JOIN in PostgreSQL
What is RIGHT JOIN?
RIGHT JOIN, also known as RIGHT OUTER JOIN, is used to retrieve all records from the right-side table and matching records from the left-side table.
When a matching record does not exist in the left-side table, PostgreSQL returns NULL for the columns belonging to that table.
To understand this concept, imagine that an e-commerce company has two tables: customers and orders.
The customers table stores customer information, while the orders table stores customer purchases.
The customers table contains three records.
| customer_id | name |
| 1 | Rahul |
| 2 | Priya |
| 3 | Amit |
The orders table contains three records.
| order_id | customer_id | amount |
| 101 | 1 | 2000 |
| 102 | 2 | 3500 |
| 103 | 4 | 1500 |
Notice that order 103 belongs to customer ID 4, but customer ID 4 does not exist in the customers table.
Now suppose the company wants to display every order, including orders that do not have a matching customer record.
This is where RIGHT JOIN becomes useful.
RIGHT JOIN Syntax
SELECT
columns
FROM left_table
RIGHT JOIN right_table
ON left_table.common_column = right_table.common_column;
Practical PostgreSQL Example
SELECT
c.customer_id,
c.name,
o.order_id,
o.amount
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;
Understanding the Query
Here, customers is the left-side table and orders is the right-side table.
The condition c.customer_id = o.customer_id tells PostgreSQL how the two tables are related.
PostgreSQL finds matching customer records for every order. If a matching customer is unavailable, the customer columns contain NULL.
The expected result is:
| customer_id | name | order_id | amount |
| 1 | Rahul | 101 | 2000 |
| 2 | Priya | 102 | 3500 |
| NULL | NULL | 103 | 1500 |
The important thing to understand is that RIGHT JOIN does not mean that PostgreSQL only returns matching records. It preserves every record from the right-side table.
RIGHT JOIN vs LEFT JOIN
RIGHT JOIN and LEFT JOIN are closely related operations.
LEFT JOIN preserves all records from the left-side table, whereas RIGHT JOIN preserves all records from the right-side table.
In fact, we can rewrite a RIGHT JOIN as a LEFT JOIN simply by changing the order of the tables.
For example:
SELECT
c.customer_id,
c.name,
o.order_id,
o.amount
FROM orders o
LEFT JOIN customers c
ON o.customer_id = c.customer_id;
This query returns the same matching and unmatched order records as the previous RIGHT JOIN query.
In practical development, many developers prefer LEFT JOIN because it makes the main table easier to identify and keeps queries consistent.
2. Understanding FULL OUTER JOIN in PostgreSQL
What is FULL OUTER JOIN?
FULL OUTER JOIN is used when we want to retrieve all records from both tables, regardless of whether matching records exist.
It combines the results of matching records, unmatched records from the left table, and unmatched records from the right table.
When PostgreSQL cannot find a matching record, it fills the missing columns with NULL.
Let's understand this using our customers and orders example.
The customers table contains Rahul, Priya, and Amit.
The orders table contains orders associated with customer IDs 1, 2, and 4.
Customer ID 3 does not have an order, and order 103 does not have a matching customer.
If we use INNER JOIN, we only get Rahul and Priya because they have matching records.
If we use LEFT JOIN, we get all customers, including Amit, who has no order.
If we use RIGHT JOIN, we get all orders, including order 103, which has no matching customer.
But what if we want both unmatched records along with all matching records?
We use FULL OUTER JOIN.
FULL OUTER JOIN Syntax
SELECT
columns
FROM left_table
FULL OUTER JOIN right_table
ON left_table.common_column = right_table.common_column;
Practical PostgreSQL Example
SELECT
c.customer_id,
c.name,
o.order_id
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;
Expected Output
| customer_id | name | order_id |
| 1 | Rahul | 101 |
| 2 | Priya | 102 |
| 3 | Amit | NULL |
| NULL | NULL | 103 |
The first two records are matching records. The third record represents a customer without an order, and the fourth record represents an order without a matching customer.
This makes FULL OUTER JOIN particularly useful when comparing two datasets and identifying missing or unmatched information.
Important PostgreSQL Concept
PostgreSQL directly supports FULL OUTER JOIN. We do not need to combine LEFT JOIN and RIGHT JOIN using UNION to simulate it.
For example, the following query is already sufficient:
SELECT * FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
UNION is a separate SQL set operator that combines the results of multiple SELECT statements. It is not required for a normal FULL OUTER JOIN in PostgreSQL.
3. Understanding UNION in PostgreSQL
Although we do not need UNION to perform FULL OUTER JOIN in PostgreSQL, it is still an important SQL concept.
UNION combines the results of two or more SELECT queries into a single result set.
For example, suppose we want to combine the names of customers from two different queries.
SELECT name FROM customers WHERE city = 'Ranchi' UNION SELECT name FROM customers WHERE city = 'Delhi';
UNION combines the results and removes duplicate rows.
PostgreSQL also supports UNION ALL, which combines the results without removing duplicate rows.
SELECT name FROM customers WHERE city = 'Ranchi' UNION ALL SELECT name FROM customers WHERE city = 'Delhi';
Both queries must return the same number of columns, and their corresponding columns must have compatible data types.
The important distinction is that UNION combines the results of separate SELECT statements, whereas JOIN combines related columns from different tables.
4. Understanding SELF JOIN in PostgreSQL
What is SELF JOIN?
SELF JOIN is a technique in SQL in which a table is joined with itself.
It is useful when records in the same table have relationships with other records in that table.
Consider an organization where employees report to managers. Employee information and manager information can both be stored in the same employees table.
Instead of creating a separate managers table, we can store the manager's employee ID in a column called manager_id.
For example:
| emp_id | emp_name | manager_id |
| 1 | Amit | NULL |
| 2 | Rahul | 1 |
| 3 | Priya | 1 |
| 4 | Sneha | 2 |
| 5 | Karan | 2 |
Here, Amit is the top-level manager. Rahul and Priya report to Amit, while Sneha and Karan report to Rahul.
The manager_id column refers to another employee's emp_id in the same table.
This creates a relationship between records within one table.
SELF JOIN Syntax
SELECT
A.column_name,
B.column_name
FROM table_name A
JOIN table_name B
ON A.common_column = B.common_column;
The important concept here is table aliases.
We use aliases to refer to the same table in two different ways.
For example, employees e represents the employee, while employees m represents the manager.
These are not two separate database tables. They are two references to the same employees table.
Practical PostgreSQL Example
SELECT
e.emp_name AS employee,
m.emp_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
Expected Output
| employee | manager |
| Amit | NULL |
| Rahul | Amit |
| Priya | Amit |
| Sneha | Rahul |
| Karan | Rahul |
Understanding the Query
The first reference, employees e, represents the employee record.
The second reference, employees m, represents the manager record.
The condition e.manager_id = m.emp_id connects the employee with the corresponding manager.
We use LEFT JOIN because we want every employee to appear in the output, including Amit, who does not have a manager.
If we used INNER JOIN, Amit would disappear from the result because his manager_id is NULL and no matching manager record exists.
Real-World Applications of SELF JOIN
SELF JOIN is useful in organizational management systems where employees report to managers. It is also useful in category management systems where one category has a parent category, educational platforms where students are assigned mentors, and product systems where products have related products.
The important point is that SELF JOIN is not a separate JOIN keyword. It is a technique that uses aliases to join a table with itself.
5. Understanding CROSS JOIN in PostgreSQL
What is CROSS JOIN?
CROSS JOIN is used to generate the Cartesian product of two tables.
It means every record from the first table is combined with every record from the second table.
Unlike INNER JOIN, LEFT JOIN, and RIGHT JOIN, CROSS JOIN does not require an ON condition.
Let's understand this with a simple example.
Suppose we have a colors table.
| color |
| Red |
| Blue |
We also have a sizes table.
| size |
| Small |
| Medium |
| Large |
Now imagine that a clothing company wants to generate every possible color and size combination for its products.
Instead of manually creating these combinations, we can use CROSS JOIN.
CROSS JOIN Syntax
SELECT
columns
FROM first_table
CROSS JOIN second_table;
Practical PostgreSQL Example
SELECT
c.color,
s.size
FROM colors c
CROSS JOIN sizes s;
Expected Output
| color | size |
| Red | Small |
| Red | Medium |
| Red | Large |
| Blue | Small |
| Blue | Medium |
| Blue | Large |
Understanding the Cartesian Product
The total number of rows generated by CROSS JOIN depends on the number of records in both tables.
The formula is:
Total Rows = Rows in First Table × Rows in Second Table
In our example, the colors table contains 2 records, and the sizes table contains 3 records.
Therefore:
2 × 3 = 6 rows.
Now suppose the first table contains 4 records and the second table contains 5 records.
The CROSS JOIN will generate:
4 × 5 = 20 rows.
This is why we must use CROSS JOIN carefully when working with large tables. If two tables contain thousands of records, the resulting Cartesian product can become extremely large.
Real-World Applications of CROSS JOIN
CROSS JOIN is useful when generating product variations, creating combinations of dates and time slots, generating test datasets, and preparing combinations for analytical reports.
For example, an online clothing store can use CROSS JOIN to generate all possible combinations of product colors and available sizes.
6. Joining More Than Two Tables in PostgreSQL
Can We Join Three or More Tables?
Yes. PostgreSQL allows us to join multiple tables in a single SQL query.
In real-world applications, information is usually distributed across several related tables.
For example, an e-commerce application may store customer information in the customers table, order information in the orders table, and product information in the products table.
If we want to display the customer name, order ID, product name, and product price together, we need to join all three tables.
Understanding the Database Relationship
The customers table contains customer_id, which identifies each customer.
The orders table contains customer_id and product_id, which connect orders to customers and products.
The products table contains product_id, which identifies each product.
The relationship works as follows:
Customers → Orders → Products
First, we connect customers with orders using customer_id. Then we connect orders with products using product_id.
Practical PostgreSQL Example
SELECT
c.name,
o.order_id,
p.product_name,
p.price
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
INNER JOIN products p
ON o.product_id = p.product_id;
Expected Output
| name | order_id | product_name | price |
| Rahul | 101 | Laptop | 50000 |
| Rahul | 102 | Keyboard | 1500 |
| Priya | 103 | Mobile | 20000 |
The exact output depends on the records inserted into the database.
The important concept is that we can continue adding JOIN operations whenever we need information from additional related tables.
Why Are Multiple JOINs Important?
Multiple JOINs are commonly used in e-commerce applications, banking systems, school management software, hospital management systems, inventory management platforms, and business intelligence dashboards.
A developer might need information from five or more tables to generate a single report. Understanding multiple JOINs is therefore an essential SQL skill.
7. Combining JOIN, WHERE, GROUP BY, HAVING and ORDER BY
This is one of the most important concepts in practical SQL development because real-world applications often require us to retrieve related information, filter records, calculate totals, group data, and sort results.
Let's understand each clause using an e-commerce example.
Suppose a company wants to calculate how much each customer has spent.
The customers table stores customer details, while the orders table stores purchase amounts.
Understanding JOIN
JOIN combines related records from multiple tables.
For example:
SELECT
c.name,
o.amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;
This query connects customers with their orders.
Understanding WHERE
WHERE filters individual rows before grouping takes place.
For example, suppose we only want orders where the amount is greater than or equal to 1000.
SELECT
c.name,
o.amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.amount >= 1000;
Orders below 1000 will not be included in the result.
Understanding GROUP BY
GROUP BY combines records that have the same value into groups.
Suppose Rahul has placed multiple orders. Instead of displaying every individual order, we want to calculate Rahul's total spending.
SELECT
c.name,
SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
Here, SUM calculates the total amount for each customer.
We group by both customer_id and name so that each customer is represented by their own group.
Understanding HAVING
HAVING filters grouped results.
Suppose we only want customers whose total spending is greater than or equal to 3000.
SELECT
c.name,
SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING SUM(o.amount) >= 3000;
The important difference is that WHERE filters individual rows, while HAVING filters groups after aggregation.
Understanding ORDER BY
ORDER BY sorts the final results.
For example, we can display customers from highest spending to lowest spending.
SELECT
c.name,
SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING SUM(o.amount) >= 3000
ORDER BY total_spent DESC;
DESC means descending order, while ASC means ascending order.
Complete Practical Query
Now let's combine everything into one query.
SELECT
c.name,
COUNT(o.order_id) AS total_orders,
SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.amount >= 1000
GROUP BY c.customer_id, c.name
HAVING SUM(o.amount) >= 3000
ORDER BY total_spent DESC;
The query first connects the customers and orders tables. It then filters out individual orders below 1000, groups the remaining records by customer, calculates the total number of orders and spending, removes groups with spending below 3000, and finally sorts the results by total spending.
SQL Query Processing Order
The logical processing order of the important clauses is:
FROM and JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
Understanding this sequence makes it much easier to write complex SQL queries and troubleshoot unexpected results.
8. Common JOIN Mistakes in PostgreSQL
Even experienced developers can make mistakes when writing JOIN queries. Understanding these mistakes helps us write accurate and reliable SQL statements.
Missing the ON Condition
One common mistake is forgetting to specify the relationship between two tables.
Incorrect query:
SELECT * FROM customers c JOIN orders o;
A qualified INNER JOIN requires a join condition, so this query is incomplete and PostgreSQL will return a syntax error.
Correct query:
SELECT * FROM customers c JOIN orders o ON c.customer_id = o.customer_id;
The ON condition defines how records from the two tables are matched.
Using the Wrong JOIN Type
Suppose a company wants to display every customer, including customers who have never placed an order.
If we use INNER JOIN, customers without matching orders will disappear.
The correct solution is LEFT JOIN.
SELECT
c.name,
o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;
This ensures that every customer appears in the result.
Ambiguous Column Names
An ambiguous column error occurs when multiple tables contain a column with the same name and we reference that column without specifying which table it belongs to.
Incorrect query:
SELECT customer_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id;
Both tables contain customer_id, so PostgreSQL cannot determine which column we intend to select.
Correct query:
SELECT c.customer_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id;
Using table aliases makes SQL queries clearer and prevents ambiguity.
Forgetting That JOINs Can Produce Duplicate Records
Suppose Rahul has placed three orders.
When we join customers and orders, Rahul's name can appear three times because each order creates a separate matching row.
This is not necessarily an error. It is the expected behavior of a one-to-many relationship.
If we want to count orders instead of displaying every order, we should use COUNT with GROUP BY.
SELECT
c.name,
COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
We should not use DISTINCT simply to hide duplicate-looking rows without understanding why those rows exist.
Filtering LEFT JOIN Results Incorrectly
This is one of the most important JOIN concepts to understand.
Consider the following query:
SELECT
c.name,
o.order_id,
o.amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.amount > 1000;
At first glance, this query appears to retrieve all customers and their orders above 1000.
However, the WHERE condition removes rows where o.amount is NULL.
As a result, customers without matching orders disappear.
If our goal is to preserve every customer while matching only orders above 1000, we should place the amount condition inside the ON clause.
Correct query:
SELECT
c.name,
o.order_id,
o.amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
AND o.amount > 1000;
Now all customers remain in the result. Orders below or equal to 1000 are not matched, and their order columns appear as NULL.
This demonstrates an important difference between filtering rows using WHERE and restricting matching records using ON.
9. Understanding the Difference Between SQL JOIN Types
Different JOIN types solve different problems.
INNER JOIN returns only matching records from both tables.
LEFT JOIN returns all records from the left table and matching records from the right table.
RIGHT JOIN returns all records from the right table and matching records from the left table.
FULL OUTER JOIN returns all records from both tables, including unmatched records.
SELF JOIN connects related records within the same table.
CROSS JOIN generates every possible combination of records from two tables.
Choosing the correct JOIN depends on the information we want to retrieve.
For example, if we want only customers who have placed orders, INNER JOIN is appropriate. If we want every customer, including those who have never purchased anything, LEFT JOIN is appropriate. If we want to identify unmatched records from both datasets, FULL OUTER JOIN is appropriate.
10. Conclusion
SQL JOINs are among the most important concepts in relational database management systems. Understanding them allows developers to retrieve meaningful information from multiple tables without storing all information in a single table.
In this lesson, we explored RIGHT JOIN, FULL OUTER JOIN, UNION, SELF JOIN, and CROSS JOIN using PostgreSQL. We also learned how to combine multiple tables and use JOIN with WHERE, GROUP BY, HAVING, and ORDER BY.
We discussed common mistakes, including missing ON conditions, ambiguous column names, incorrect JOIN selection, unexpected duplicate records, and filtering LEFT JOIN results incorrectly.
The best way to master SQL JOINs is through consistent practical exercises. Create multiple related tables, insert sample records, write queries, observe the results, and experiment with different JOIN types.
Once you understand these concepts, you will be able to work with complex relational databases and build practical applications using PostgreSQL.
Assignment 27 – SQL JOINs Part 2
Topic: Advanced SQL JOINs in PostgreSQL
Total Questions: 10
Database: E-Commerce Database
Question 1 – RIGHT JOIN
Write a PostgreSQL query to display all orders along with their customer names using RIGHT JOIN. Make sure that orders without matching customers are also displayed.
Question 2 – FULL OUTER JOIN
Write a query using FULL OUTER JOIN to display all customers and all orders, including customers who have never placed an order and orders that do not have a matching customer.
Question 3 – SELF JOIN
Create an employees table containing emp_id, emp_name, and manager_id. Insert at least five employees and write a SELF JOIN query to display every employee with their manager's name.
Question 4 – CROSS JOIN
Create two tables named colors and sizes. Insert at least four colors and three sizes. Write a CROSS JOIN query to generate every possible color and size combination.
Question 5 – Multiple Table JOIN
Write a query to retrieve customer names, order IDs, product names, and product prices by joining customers, orders, and products.
Question 6 – JOIN with WHERE
Write a query to display customer names, order IDs, and order amounts where the order amount is greater than 2000.
Question 7 – JOIN with GROUP BY
Write a query to calculate the total number of orders placed by each customer and display the customer name along with the order count.
Question 8 – JOIN with HAVING
Write a query to calculate the total spending of each customer and display only those customers whose total spending is greater than 5000.
Question 9 – JOIN with ORDER BY
Write a query to display customer names and their total spending, sorted from highest spending to lowest spending.
Question 10 – Advanced E-Commerce Analysis
Write a complete SQL query using JOIN, WHERE, GROUP BY, HAVING, and ORDER BY to display customer names, total orders, and total spending. Include only orders greater than or equal to 1000, display customers whose total spending is greater than or equal to 3000, and sort the result by total spending in descending order.



