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

In this article, we are going to see how we can compare the rows and columns of the same table in SQL. We will be taking a few examples to see how we can do this in different ways.

To create the database:

Syntax :
CREATE database_name;

CREATE Shopping

To use this database:

Syntax :
USE database_name;

USE Shopping

Creating Table for making queries:

Syntax :
CREATE TABLE table_name(column1 TYPE, column2 TYPE ... );

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

Inserting records in the table student:

Syntax :
INSERT INTO table_name(column1, column2 ...) VALUES (value1, value2 ...);

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');

Example 1:

Comparing rows of the same table. In the example, we are comparing the immediate rows to calculate the sales made on a day by comparing the amounts of two consecutive days.

Syntax for inner join :
SELECT column_name(s)
FROM table1 t1 INNER JOIN table1 t2 
on t1.column1 = t2.column1;

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 :

Example 2:

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 for self join :
SELECT column_name(s)
FROM table1 t1, table1 t2
WHERE condition1 and condition2 ... ;

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 :

Example 3:

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.

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 :