In this tutorial, we will study how to compare successive rows within the same table in MySQL. Here we will use Self Join and Inner join to compare successive rows within the same table.
SELF JOIN
A table can be joined to itself using a join called a SELF JOIN. The self-join is frequently used to compare a record to other rows in the same table or to query hierarchical data. Use table aliases to avoid using the same table name twice in a single query when doing a self-join. Be aware that using table aliases to reference a table more than once in a query can result in an error.
SYNTAX
Select … FROM table_name AS S1
INNER JOIN table_name AS S2;Code language: PHP (php)INNER JOIN
When using the MySQL Inner Join, other rows and columns are hidden and only those results from the tables that fit the stated condition are returned. It is not required to use the Inner Join keyword with the query because MySQL treats it as a default Join.
SYNTAX
SELECT columns
FROM table_name1
INNER JOIN table_name2 ON condition1
INNER JOIN table_name3 ON condition2 Examples of comparing successive rows using INNER JOIN
First of all, we will create a table setup that will store the id, date, product name, and quantity of the product. Also insert values into it.
CREATE TABLE setup (
id INT AUTO_INCREMENT PRIMARY KEY,
current date NOT NULL,
product VARCHAR(200) NOT NULL,
amount INT(50) NOT NULL
);
INSERT INTO setup(current, product, amount)
VALUES('2022-11-01', 'A', 35),
('2022-11-02', 'A', 30),
('2022-11-03', 'A', 45),
('2022-11-04', 'A', 60),
('2022-11-05', 'A', 50);
SELECT * FROM setup;Code language: PHP (php)Output-
Now we will check how many items are being received per day for each item and for this we will need to compare the quantity of a particular day with the previous day. This means that we will compare a row with its successive row.
With the understanding that there are no gaps in the id columns, the condition in the INNER JOIN clause a2.id = a1.id + 1 enables you to compare the current entry with the following row in the setup database.
Examples of comparing successive rows using SELF JOIN
Now we will create another table named customer which stores id, name of the customer, date, city, and amount of the product. Also, insert the values into it.
CREATE TABLE customer(
id INT AUTO_INCREMENT PRIMARY KEY,
date DATE,
amount INT,
name VARCHAR(50),
city VARCHAR(50));
INSERT INTO customer(date, amount, name, city)
VALUES ('2022-10-01',200, 'john', 'london'),
('2022-10-02',225,'philips', 'ohio'),
('2022-10-03',340,'saje', 'barkley'),
('2022-10-04',360, 'tomy', 'california'),
('2022-11-02',228,'tucky', 'ohio'),
('2022-09-04',350, 'william', 'california');
Code language: JavaScript (javascript)Now we will compare all the customers that are in the same city using SELF JOIN.
SELECT A.name AS CustomerName1, B.name AS CustomerName2, A.city
FROM customer A, customer B
WHERE A.id <> B.id
AND A.city = B.city
ORDER BY A.city;Code language: PHP (php)Output-
Conclusion
In this tutorial, we learned how to compare successive rows within the same table using joins. In MySQL, comparing successive rows within the same table is only possible if the table is indexed. The comparison is done by comparing the value of the column in the first row of the table to the value of the column in the last row of the table. If the values are the same, then the row is considered to be a match.