A small correction: There was no need of multiplying by 1.0 is half salary. When we divide integer by integer then it returns integer, so 3/2 will return 1 and 2/3 will return 0. In our case all salaries are divisible by 2 so there will not be any data loss and hence *1.0 not required. Master the art of SQL @ Rs 1999 with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch. www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354 Some salient features of the course: 1- No prerequisite. All concepts have been covered from absolute basics. 2- Course contains 2 portfolio projects 3- 100+ interview problems to crack any SQL INTERVIEW 4- A TRUE bonus of 5000 (access to premium account to a SQL practice website). 5- You will also be part of premium data community where you can ask any doubts. 6- A bonus session on Tableau. #sql #analytics
@@ankitbansal6hey the course also contains the videos that you post on TH-cam like faang interview questions and one that you posted above..order or Execution?
Hi @ankitbansal6 I follow your videos and learn a lot from them in terms of quick tricks to solve complex SQL problems. In this video, I have a question - why did you say "join" would run before "where" in the order of execution because in my knowledge all databases have Predicate Pushdown feature that they apply to the queries to filter out number of rows using "where" to make life easy for the "join" clause (by making less number of rows to get scanned)? Second question - how does "outer joins" work when there are more than 2 tables? Say table A left join table B left join table C. In this does table C get joined with table B or table A or both?
If you want to push then give your filter condition in the join clause itself. Because your filter might be based on the 2 tables getting joined . So unless they are joined you can't put a filter. For your second question..it depends on your join condition . In on clause which columns you join accordingly tables will join.
Need more clarification on one thing, after group by, aggregation calculation should happen because we can apply filter on aggregated function using Having clause. We cannnot use alais of aggregated function in Having clause because when col is getting displayed it is giving that name. So order of execution would be where --> group by --> aggregation --> having --> select ... Is this correct ?
Ankit, one que - we sometimes use 'And' just after the on condition while joining two tables, so wanted to know if 'And' executes first or 'Where' executes first?
Hello Ankit Sir, It's been a great learning experience from your videos, thanks a lot for all the effort you put in making this content for us😇. I have two questions regarding the order of execution, 1. You mentioned that GROUP BY comes before SELECT. But what about those situations, when we use GROUP BY clause on the number of the column (i.e., 1, 2, 3). My question is, if GROUP BY clause gets the preference then how is SQL is able to determine the order of the columns in SELECT clause. 2. My second question is about the Subquery and nested query order of execution.
"I am from a non-IT background, with my major in Economics. I have worked in various job roles with different companies in the past. I'm interested in transitioning my career to become a data analyst. If I want to pursue this path, how should I proceed, and how can I become proficient in SQL, like you, sir?"
Hi Ankit, please provide the answer. Question : what is the order of excavation when window function exist in SQL Query. This question is asked in interview.
hello Ankit sir, could you please help me with a query, i.e., if i have to count only integers/numbers in a column which is inclusive of null value, blank, alphabets etc, if i do have to count only numbers so shall i use "= TRUE" to count or is there any other functions?
Very informative. Thank you. What happens if we use windows functions like rank, what would be the order of execution, also could you also do a video on how costly (time consuming each operation is and provide insights on topics like why CTEs are preferred over sub-queries?)
Hi Ankit. ty for the awesome videos. I have a question. In this sql query : SELECT TOP 3 * FROM dbo.emp ORDER BY salary DESC . What will be the execution plan . To me,it looks like order by is running before select. Could you please explain.
Only one thing you missed: where do window functions come in the execution order? I tried to think like query optimiser and I'm assuming it gets applied along with select since it is a part of the statement. So it will be from, where, group by, having, select, window functions, order by, limit/top, right? Kindly confirm. Also, thanks again for the best comprehensive collection of SQL stuff on TH-cam. I'm a beginner but I feel confident to even tackle a intermediate stage interview because of your material. God bless!
Hi Ankit I have a query, you mentioned that SELECT execution will be after GROUP BY statement. In below example, a new custom column(decade) was added using CASE in SELECT and its used in GROUP BY. At the time of GROUP BY statement execution, new column(decade) won't be available right and it should cause error. But in this case its working, i'm confused. Would you be able to explain what is happening here please? - Thank you! SELECT CASE WHEN years BETWEEN 2010 AND 2019 THEN '2010-2019' WHEN years BETWEEN 2020 AND 2029 THEN '2020-2029' ELSE 'Other' END AS decade, SUM(revenue) AS total_revenue FROM meta_revenue GROUP BY decade;
@@vimalvikashs741Order of execution is different in different databases. In some databases you will see group by 1,2..... works and you can write an alias name in having clause but the same is not possible in SQL Server.
hello ankit, when will agreagation func will execute- sum (salary ) in the order of execution. beacsue if it execute along with select statement then select statement comes after having so how it will execute please explain ?
İnstead of writing join join can be done in where clause so i am not sure whether join before to where clause or it is in the same order to where clause.
Interesting point. I think when 'join' comes in the statement, it will be coming first and then the 'where' acts on the joined table. But when we use the where command to join (for example: select * from table1, table2 where table1.id=table2.id), then the where itself is used to create the join so where will happen first.
Respected Sir, Best videos for learning SQL, PLEASE make video on triggers in detail but in hindi if possible please. And if possible how I send messages on WhatsApp while new record insert into sql server database table please
What about aggregate func...will it come after having..from ,join, where, group by,having, select,order by,top/agg func/analytical func... correct me if i am wrong
Hi Sir, I had a small doubt some people write group by 1,2 while writing the query. How will it understand what is column 1 and 2 it is referring to as select statement is not executed yet. Thank you
No logically it wouldn't make sense since the where is being performed on the combined tables and not a single table, and for combination to happen it should be join coming first before the where
🐍 Explore the world of Python for Research Work with this amazing playlist! 📚🔬 Don't forget to hit the 'Like' button 👍 and 'Subscribe' for more awesome content! 🤩 Check it out: th-cam.com/play/PLIDulLqbs-nnr758jI13Cm5RmsOTkOk7t.html
JOIN clause: If there are JOIN clauses in your query, they are executed after the filtering in the WHERE clause. JOINs are used to combine rows from different tables based on specified conditions, such as INNER JOIN, LEFT JOIN, etc.
A small correction: There was no need of multiplying by 1.0 is half salary. When we divide integer by integer then it returns integer, so 3/2 will return 1 and 2/3 will return 0. In our case all salaries are divisible by 2 so there will not be any data loss and hence *1.0 not required.
Master the art of SQL @ Rs 1999 with my zero to hero SQL course. The course is focused on data analytics and covers all the advanced concepts starting from scratch.
www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354
Some salient features of the course:
1- No prerequisite. All concepts have been covered from absolute basics.
2- Course contains 2 portfolio projects
3- 100+ interview problems to crack any SQL INTERVIEW
4- A TRUE bonus of 5000 (access to premium account to a SQL practice website).
5- You will also be part of premium data community where you can ask any doubts.
6- A bonus session on Tableau.
#sql #analytics
I am currently a MSSQL DBA, should I take this course ? Will it be beneficial for me ? Any sample video from the course ?
@@simondungeon1218 the course will help you in your data analytics journey for sure
I have the course but donot have access to SQL practice website .please help me here
@@Adityakumar-wp2mk check the project folder
@@ankitbansal6hey the course also contains the videos that you post on TH-cam like faang interview questions and one that you posted above..order or Execution?
before this video ,I was memorizing these this and forgot every time. but after watching this video my all concepts has cleared . I will never forget.
Thanks for this amazing video, before this I was confused about why we can't use aliases with having clause. Now it's clear
The Way of each and every query statement explanation just at another level... thank you!
Simple and easy to understand thanks for clearing all my doubts related to order of execution ❤
Great explanation, thanks for sharing valuable information in a simple way...
I watched the video sit for kpmg and git offer. Thanks lot. Just brushedup my sql skills
Great job 👏
Great effort towards sharing knowledge. Great 👍
Tq bro it's sounds great
Amazing video, thanks for making it so easy to understand, all of your videos are amazing
Glad you like them!
thank you soo much sir really helpful!
Most welcome!
All the people who's learning SQL should watch this video
Very nice logic. I was thinking like that, but I never explicitly stated it like that. I've subscribed to your channel. I like your approach.
Great Explanation. Appreciate you effort.
great lecture
Real good video Ankit , helped a lot in understanding basics
Learnt a lot. Hope we get more videos on Namaste SQL. This is going to be huge ❤
Another great explanation 🔥
This is an amazing video
Great explanation of concepts,
Thanks a lot for this sir!!
Keep watching
Unbeatable Content😀
Hi @ankitbansal6 I follow your videos and learn a lot from them in terms of quick tricks to solve complex SQL problems. In this video, I have a question - why did you say "join" would run before "where" in the order of execution because in my knowledge all databases have Predicate Pushdown feature that they apply to the queries to filter out number of rows using "where" to make life easy for the "join" clause (by making less number of rows to get scanned)?
Second question - how does "outer joins" work when there are more than 2 tables? Say table A left join table B left join table C. In this does table C get joined with table B or table A or both?
If you want to push then give your filter condition in the join clause itself. Because your filter might be based on the 2 tables getting joined . So unless they are joined you can't put a filter.
For your second question..it depends on your join condition . In on clause which columns you join accordingly tables will join.
I was always having doubt, why can't the alias of aggregated column can be used with having? You've cleared this doubt. Thanks ❤
Thanks 👍🙏
Crystal clear Ankit bro 💯 thanks for sharing your knowledge, keep going...👍
My pleasure 😊 keep watching ✌️
Good one...
finished watching
Hi Ankit, this was an awesome video. Just one thing, I guess you missed order of execution of window functions in this.
That comes in select so runs along with select
Need more clarification on one thing, after group by, aggregation calculation should happen because we can apply filter on aggregated function using Having clause. We cannnot use alais of aggregated function in Having clause because when col is getting displayed it is giving that name. So order of execution would be where --> group by --> aggregation --> having --> select ... Is this correct ?
Ankit, one que - we sometimes use 'And' just after the on condition while joining two tables, so wanted to know if 'And' executes first or 'Where' executes first?
Check this out
th-cam.com/video/Ypwfq5tNw-4/w-d-xo.html
Hello Ankit Sir,
It's been a great learning experience from your videos, thanks a lot for all the effort you put in making this content for us😇.
I have two questions regarding the order of execution,
1. You mentioned that GROUP BY comes before SELECT. But what about those situations, when we use GROUP BY clause on the number of the column (i.e., 1, 2, 3).
My question is, if GROUP BY clause gets the preference then how is SQL is able to determine the order of the columns in SELECT clause.
2. My second question is about the Subquery and nested query order of execution.
It will just pick the column list from select but execution will be later.
For sub query always the inner query will run first and then outer one.
Okay, thank you so much sir.
"I am from a non-IT background, with my major in Economics. I have worked in various job roles with different companies in the past. I'm interested in transitioning my career to become a data analyst. If I want to pursue this path, how should I proceed, and how can I become proficient in SQL, like you, sir?"
Hi Ankit, please provide the answer. Question : what is the order of excavation when window function exist in SQL Query. This question is asked in interview.
Along with select
@@ankitbansal6 thanks
hello Ankit sir, could you please help me with a query, i.e., if i have to count only integers/numbers in a column which is inclusive of null value, blank, alphabets etc, if i do have to count only numbers so shall i use "= TRUE" to count or is there any other functions?
Hi Ankit Sir , what about DISTINCT clause ? order of execution will be same as SELECT ?
Yes, correct
Very informative. Thank you.
What happens if we use windows functions like rank, what would be the order of execution, also could you also do a video on how costly (time consuming each operation is and provide insights on topics like why CTEs are preferred over sub-queries?)
Window functions will be executed along with select.
Sure.
Hi Ankit. ty for the awesome videos. I have a question. In this sql query : SELECT TOP 3 * FROM dbo.emp ORDER BY salary DESC . What will be the execution plan . To me,it looks like order by is running before select. Could you please explain.
First select then order by then top
Only one thing you missed: where do window functions come in the execution order? I tried to think like query optimiser and I'm assuming it gets applied along with select since it is a part of the statement. So it will be from, where, group by, having, select, window functions, order by, limit/top, right? Kindly confirm. Also, thanks again for the best comprehensive collection of SQL stuff on TH-cam. I'm a beginner but I feel confident to even tackle a intermediate stage interview because of your material. God bless!
Nevermind you replied in the other comments for the same question. Thanks
I was thinking the same. Can you please tell me when the data is fetched from the table? Is it before 'from'?
@@invincible9971 it is immediately after from
Hi Ankit
I have a query, you mentioned that SELECT execution will be after GROUP BY statement. In below example, a new custom column(decade) was added using CASE in SELECT and its used in GROUP BY. At the time of GROUP BY statement execution, new column(decade) won't be available right and it should cause error. But in this case its working, i'm confused. Would you be able to explain what is happening here please? - Thank you!
SELECT
CASE
WHEN years BETWEEN 2010 AND 2019 THEN '2010-2019'
WHEN years BETWEEN 2020 AND 2029 THEN '2020-2029'
ELSE 'Other'
END AS decade,
SUM(revenue) AS total_revenue
FROM meta_revenue
GROUP BY decade;
I think you are not using SQL server
yes, i'm using Postgressql. In postgressql order of execution is different?
@@vimalvikashs741Order of execution is different in different databases. In some databases you will see group by 1,2..... works and you can write an alias name in having clause but the same is not possible in SQL Server.
hello ankit, when will agreagation func will execute- sum (salary ) in the order of execution.
beacsue if it execute along with select statement then select statement comes after having so how it will execute please explain ?
ALONG WITH SELECT
İnstead of writing join join can be done in where clause so i am not sure whether join before to where clause or it is in the same order to where clause.
Interesting point. I think when 'join' comes in the statement, it will be coming first and then the 'where' acts on the joined table. But when we use the where command to join (for example: select * from table1, table2 where table1.id=table2.id), then the where itself is used to create the join so where will happen first.
where distinct comes? after select, right?
Respected Sir,
Best videos for learning SQL,
PLEASE make video on triggers in detail but in hindi if possible please. And if possible how I send messages on WhatsApp while new record insert into sql server database table please
can you please share the data set/s please
What about aggregate func...will it come after having..from ,join, where, group by,having, select,order by,top/agg func/analytical func... correct me if i am wrong
Whatever you put in select close except top will be executed along with select
@@ankitbansal6 ok
Hi Sir,
I had a small doubt some people write group by 1,2 while writing the query. How will it understand what is column 1 and 2 it is referring to as select statement is not executed yet.
Thank you
It is just based on what column we mention first , second so on
Join is costly operation. It must execute after where clause.. could you please confirm
No logically it wouldn't make sense since the where is being performed on the combined tables and not a single table, and for combination to happen it should be join coming first before the where
select name from table where salary >600 . Order of execution?
🐍 Explore the world of Python for Research Work with this amazing playlist! 📚🔬 Don't forget to hit the 'Like' button 👍 and 'Subscribe' for more awesome content! 🤩 Check it out: th-cam.com/play/PLIDulLqbs-nnr758jI13Cm5RmsOTkOk7t.html
Where should execute before join . Why join more data and then filter? Where first join next
Right. It it will have to join the tables first to apply the where clause
Select *,
row_number() over(partition by dept_id order by salary desc) as rn
from Employee
where rn
What about the window functions where do they come in order of execution @ankitbansal6
With the select clause
So basically when we select the columns we will select the windowed columns too and at that particular time only the function will be applied ?
@@RohitSinghDY exactly
JOIN clause: If there are JOIN clauses in your query, they are executed after the filtering in the WHERE clause. JOINs are used to combine rows from different tables based on specified conditions, such as INNER JOIN, LEFT JOIN, etc.
Join clause executed just after from
Where does DISTINCT come?
After select
I can share my offer letter