How to Compare Rows and Columns in the Same Table in SQL

In SQL, comparing rows and columns in the same table is a common task for data analysis, identifying relationships, and calculating differences. By using techniques like self-joins and conditional queries, we can extract meaningful insights from a single table. This enables efficient querying, allowing database administrators and developers to address various analytical and operational needs effectively.

In this article, we will explore how to compare rows and columns in SQL with examples to demonstrate various approaches. With practical demonstrations and explanations, we will learn how to make the most out of these techniques in real-world scenarios.

Examples of Compare Rows and Columns in SQL

Let’s start by creating a sample table named orders with data to use in our examples. This table will include details like order_id, order_date, amount, customer, and city. These fields allow us to explore various SQL comparison techniques effectively.

Query:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    amount INT,
    customer VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO orders(order_date, amount, customer, city) VALUES
('2020-10-01',100, 'john', 'london'),
('2020-10-02',125,'philip', 'ohio'),
('2020-10-03',140,'jose', 'barkley'),
('2020-10-04',160, 'tom', 'north carolina');
('2020-11-02',128,'duck', 'ohio'),
('2020-09-04',150, 'tucker', 'north carolina');

SELECT * FROM orders;

Output

Example 1: Comparing Rows in the Same Table

In the example, we compare rows to calculate the daily sales difference by subtracting the amount values of consecutive orders. This technique helps analyze trends or changes in sales over time by evaluating adjacent rows in the table.

Syntax

SELECT column_name(s)
FROM table1 t1 INNER JOIN table1 t2
on t1.column1 = t2.column1;

Query:

SELECT g1.order_id, g1.order_date,g1.amount, 
(g2.amount - g1.amount) AS daily_amount 
FROM orders g1 
INNER JOIN orders g2 
ON g2.order_id = g1.order_id + 1;

Output

Explanation:

• The query uses a self-join to compare the amount of one row with the amount of the next row.
  
• The difference is calculated as g2.amount - g1.amount.

Example 2: Comparing Columns in the Same Table

Comparison of columns in the same table is possible with the help of joins. Here we are comparing all the customers that are in the same city using the self join in SQL. Self-join is a regular join where a table is joined by itself. Similarly, a table may be joined with left join, right join, inner join, and full join.

Syntax

SELECT column_name(s)
FROM table1 t1, table1 t2
WHERE condition1 and condition2 ... ;

Query:

SELECT A.customer AS CustomerName1, B.customer AS CustomerName2, A.city
FROM orders A, orders B
WHERE A.order_id <> B.order_id
AND A.city = B.city 
ORDER BY A.city;

Output

Explanation:

• The self-join is used to find rows where city matches across different order_ids.
  
• The condition A.order_id <> B.order_id ensures that we are comparing different rows.

Example 3: Comparing Amounts Between Rows

In this example, we are comparing all the order_id where the amount of the first order_id is greater than the amount of the second order_id's amount. We are using the self join to perform this comparison of columns in the same table.

Query:

SELECT A.customer AS CustomerName1, B.customer AS CustomerName2, 
A.order_id AS order_id_1, B.order_id AS order_id_2, A.amount AS Amount_by_1, 
B.amount AS Amount_by_2, (A.amount - B.amount) AS difference
FROM orders A, orders B
WHERE A.order_id <> B.order_id
AND A.amount > B.amount;

Output

Explanation:

• This query identifies rows where amount in one row is greater than amount in another.
  
• The difference column shows the numerical difference between the two amounts.

Conclusion

By using techniques like self-joins and conditional comparisons, SQL makes it easy to compare rows and columns within the same table. These methods are essential for advanced data analysis, finding trends, and identifying unique patterns within a dataset. Mastering these approaches not only enhances our ability to query data efficiently but also provides powerful tools for maintaining data integrity, improving insights, and making informed decisions based on our SQL database