DEV Community

Kaziu
Kaziu

Posted on • Updated on

๐Ÿคนโ€โ™€๏ธ SELECT ORDER [SQL]

  • Why can't use SUM() in WHERE?
  • Why can't use AS in WHERE or GROUP BY?

๐Ÿ˜Ž You will get the reason why it is like that

๐Ÿ’Ž Select order

When you see this SQL, what do you think about order?

SELECT * FROM clients 
WHERE    age >= 30
GROUP BY usa
HAVING   salary > 1000
ORDER BY id desc
LIMIT    100
Enter fullscreen mode Exit fullscreen mode

Seems like sql executes from SELECT ?
actually it's not true

-- SQL executes in this order
FROM
|
WHERE
|
GROUP BY 
|
HAVING
|
SELECT
|
ORDER BY
|
LIMIT
Enter fullscreen mode Exit fullscreen mode

๐Ÿ’Ž Question 1

Why SUM() function can't use in WHERE?

FROM
|
WHERE ๐Ÿ‘ˆ ๐Ÿ‘ˆ
|
GROUP BY ๐Ÿ‘ˆ ๐Ÿ‘ˆ
|
HAVING
|
SELECT
|
ORDER BY
|
LIMIT
Enter fullscreen mode Exit fullscreen mode

Before WHERE executes, GROUP BY has not executed yet, so we can't use COUNT() SUM() in WHERE.

In other word, it's possible to use these functions in HAVING.

๐Ÿ’Ž Question 2

Why can't use AS in WHERE or GROUP BY? Even though in ORDER BY can use it, unfair!!

FROM
|
WHERE ๐Ÿ‘ˆ ๐Ÿ‘ˆ
|
GROUP BY ๐Ÿ‘ˆ ๐Ÿ‘ˆ
|
HAVING
|
SELECT ๐Ÿ‘ˆ ๐Ÿ‘ˆ
|
ORDER BY ๐Ÿ‘€ ๐Ÿ‘€ 
|
LIMIT
Enter fullscreen mode Exit fullscreen mode

take it easy, man. Because ORDER BY executes after SELECT.
You can't read name which is created by "AS" before define it.

Thank you for reading ๐Ÿค—

Discussion (0)