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 RU-vid 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.
🐍 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: ru-vid.com/group/PLIDulLqbs-nnr758jI13Cm5RmsOTkOk7t
"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?"
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, 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.
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.
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?)
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?
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 RU-vid. I'm a beginner but I feel confident to even tackle a intermediate stage interview because of your material. God bless!
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
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
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.
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 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.
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
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
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.
