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: