Subquery in SQL | Correlated Subquery + Complete SQL Subqueries Tutorial
techTFQ・74 minutes read
The video sponsored by Brilliant explains subqueries in SQL, showcasing different types of subqueries and providing practical examples. Subqueries can be used to filter and compare data in SQL queries, improving query efficiency and readability, with techniques like correlated and nested subqueries highlighted.
Insights
- Subqueries in SQL can be used within other SQL queries to break down complex problems into manageable parts, facilitating easier query solving and dynamic query writing without hardcoded values.
- Correlated subqueries are crucial for comparing data between tables, executing for each record from the outer query and filtering results based on specific conditions, although they can be inefficient for large datasets.
Get key ideas from YouTube videos. It’s free
Recent questions
What are sub queries in SQL?
Sub queries are SQL queries within other SQL queries, allowing for more complex data analysis and comparisons.
How can sub queries be used in SQL?
Sub queries can be used to break down queries into parts for easier solving, write dynamic queries to avoid hardcoding values, and compare data between tables.
What is the difference between inner and outer queries?
Inner queries are executed first and provide values for outer queries, while outer queries depend on inner query results for processing.
How can sub queries be utilized in SQL commands?
Sub queries can be used in SQL commands like insert, update, and delete to load data into new tables, update existing data based on specific criteria, and filter records for deletion.
What is the significance of correlated subqueries in SQL?
Correlated subqueries are essential for comparing data between tables, executing for every record from the outer query, and filtering out specific data based on conditions.
Related videos
Summary
00:00
Understanding Sub Queries in SQL Video
- Sub queries in SQL are explained in the video sponsored by Brilliant, an online learning platform.
- The video starts by defining sub queries as SQL queries within other SQL queries.
- Different types of sub queries in SQL are discussed, along with examples of writing SQL queries using them.
- The video explains where sub queries can be used and the SQL commands that support sub queries.
- A practical example is given using an employee table to find employees earning more than the average salary.
- The importance of breaking down queries into parts for easier solving is highlighted.
- The video demonstrates writing a dynamic query using sub queries to avoid hardcoding values.
- The concept of inner and outer queries is explained, with the inner query being executed first.
- Scalar sub queries, which return one row and one column, are detailed with examples.
- Multiple row sub queries, which can return multiple columns and rows or one column with multiple rows, are explained with an example of finding the highest salary in each department.
13:56
Max Salary by Department with SQL Subquery
- To find the highest salary for each department, use a group by statement to group data by unique department names and then find the maximum salary within each group.
- Execute the group by statement on the department name column to create groups for each unique department, using the max aggregate function to determine the maximum salary within each group.
- The result will display the maximum salary for each department, such as finance with a maximum salary of 6500 and admin with 5000.
- To filter data from the employee table based on the maximum salary for each department, construct a query using the select statement and add a filter condition considering both the department name and salary columns.
- Use an in clause in the filter condition to accommodate multiple values returned by the subquery, ensuring compatibility with the multiple rows returned.
- Executing this query will yield five different records, showcasing employees in each department earning the highest salaries, like Darwin and Satya in finance earning 6500.
- SQL processes the subquery first, holding its values before matching the filter condition with the combination of department name and salary from the employee table.
- The subquery returns multiple rows but only one column, allowing comparison with any column in the main query, exemplifying a single column multiple row subquery.
- For a correlated subquery, which is related to the outer query, the processing depends on the values returned from the outer query, unlike scalar or multiple row subqueries.
- To find employees in each department earning more than the average salary in that department, use a correlated subquery to calculate the average salary specific to each department and compare it with individual salaries.
28:01
Correlated Subqueries in SQL Data Analysis
- The employee table contains 24 records, with a subquery executed for each record.
- SQL processes a correlated subquery, dependent on outer query values.
- The subquery executes 24 times, once for each record from the outer query.
- The subquery compares an employee's salary to the average salary of their department.
- Only employees with salaries exceeding their department's average are returned.
- Correlated subqueries are useful but can be inefficient for large datasets.
- A correlated subquery executes for every record from the outer query.
- Not Exists operator filters out departments without employees using a correlated subquery.
- Correlated subqueries are essential for comparing data between tables.
- Nested subqueries involve subqueries within subqueries, useful for complex data analysis.
41:53
Efficient Query Comparison Using Nested Subqueries
- The query involves comparing two parts, one being a total sales query and the other a combination of the two.
- To execute the comparison, a new query is written, selecting from the first part and joining it with the second part.
- The first part, finding total sales for each store, is placed within parentheses as a subquery named "sales."
- This subquery is then joined with the second part, named "average sales," using an alias "sales_okay."
- The join is based on the total sales to determine whose sales were better than the average sales.
- The comparison is done using a greater than symbol, comparing total sales with average sales.
- The output reveals only two stores, Apple Store 3 and Apple Store 1, with sales greater than the average.
- The query showcases nested subqueries, with one subquery inside another, and another within that.
- The text emphasizes the inefficiency of repeating subqueries and introduces the "with" clause as a better alternative.
- The "with" clause allows for naming and reusing subqueries, enhancing query efficiency and readability.
55:15
"SQL Subqueries: Comparing, Inserting, Updating Data"
- The query compares the average units sold by each store with the total items sold by each store.
- It filters records where the total items sold exceed the average units sold.
- The query results in only three stores selling more than two units, meeting the criteria.
- Subqueries are utilized within the having clause to achieve this comparison.
- The video explains the use of subqueries in SQL commands like insert, update, and delete.
- In an insert command, subqueries are used to load data into a new table from existing tables.
- The example demonstrates inserting employee data into an employee history table.
- A subquery with a not exist clause ensures no duplicate records are inserted.
- In an update command, subqueries are employed to increment salaries based on specific criteria.
- The example shows updating salaries for employees in Bangalore based on department maximums.
01:09:08
Identifying and Deleting Empty Departments
- Using a correlated subquery to identify departments without any employees, fetching only the department names, resulting in two departments with no employees. The subquery is then used to delete data from the department table related to these two departments, successfully removing two records.
