MySQL – Select statement parsing order

To write better MySQL queries, its important to know the parsing order of Select statement.

The order is as follows. It can be proved following way.

— FROM
— SELECT
— WHERE
— Group BY
— HAVING
— ORDER BY

Create a sample table and insert dummy data.

CREATE TABLE Album (
AlbumId int
,Title	varchar(100)
,ArtistId Int
,Primary Key (AlbumId)
);
INSERT INTO Album Values (1, 'A Title1', 1);
INSERT INTO Album Values (2, 'A Title1', 2);
INSERT INTO Album Values (3, 'A Title1', 3);
INSERT INTO Album Values (4, 'A Title1', 4);
INSERT INTO Album Values (5, 'A Title1', 1);
INSERT INTO Album Values (6, 'A Title1', 1);
#In following select statement, intentionally I created typos in column names.
#Try to execute the query to see the order of execution.
SELECT count(*),ArtistId1 FROM Album WHERE Title1 Like 'A%' GROUP By ArtistId1 
HAVING ArtistId1 > 3 ORDER BY ArtistId1;
# Here is the corrected query which gives result.
SELECT count(*),ArtistId FROM Album WHERE Title Like 'A%' GROUP By ArtistId 
HAVING ArtistId > 3 ORDER BY ArtistId;

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: