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 UA-cam like faang interview questions and one that you posted above..order or Execution?
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?
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.
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.
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: ua-cam.com/play/PLIDulLqbs-nnr758jI13Cm5RmsOTkOk7t.html
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 ?
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?
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
"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. 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.
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
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?)
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 ?
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.
İ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.
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
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.
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 UA-cam. I'm a beginner but I feel confident to even tackle a intermediate stage interview because of your material. God bless!
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 UA-cam like faang interview questions and one that you posted above..order or Execution?
Learnt a lot. Hope we get more videos on Namaste SQL. This is going to be huge ❤
Select *,
row_number() over(partition by dept_id order by salary desc) as rn
from Employee
where rn
Thanks for this amazing video, before this I was confused about why we can't use aliases with having clause. Now it's clear
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
ua-cam.com/video/Ypwfq5tNw-4/v-deo.html
where distinct comes? after select, right?
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.
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.
The Way of each and every query statement explanation just at another level... thank you!
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
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
Good one...
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
Great Explanation. Appreciate you effort.
I was always having doubt, why can't the alias of aggregated column can be used with having? You've cleared this doubt. Thanks ❤
🐍 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: ua-cam.com/play/PLIDulLqbs-nnr758jI13Cm5RmsOTkOk7t.html
Great explanation, thanks for sharing valuable information in a simple way...
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.
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 ?
select name from table where salary >600 . Order of execution?
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?
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
Great effort towards sharing knowledge. Great 👍
thank you soo much sir really helpful!
Most welcome!
"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. 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
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
can you please share the data set/s please
Where does DISTINCT come?
After select
I watched the video sit for kpmg and git offer. Thanks lot. Just brushedup my sql skills
Great job 👏
All the people who's learning SQL should watch this video
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.
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
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.
İ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.
Real good video Ankit , helped a lot in understanding basics
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
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.
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
Thanks 👍🙏
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 UA-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
Crystal clear Ankit bro 💯 thanks for sharing your knowledge, keep going...👍
My pleasure 😊 keep watching ✌️
finished watching
This is an amazing video
great lecture
Another great explanation 🔥
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
Great explanation of concepts,
Thanks a lot for this sir!!
Keep watching
Unbeatable Content😀
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
I can share my offer letter