xxxxxxxxxx
-- Simple query with two tables
SELECT left_table.colx, right_table.coly
FROM left_table, right_table
WHERE left_table.common_col = right_table.common_col
-- Subquery inside FROM (turning a query into virtual table using alias, evolving from the previous query)
SELECT left_table.colx, right_table.coly
FROM left_table,
(SELECT coly, colz, common_col -- subquery start
FROM another_table) AS right_table -- subquery end
WHERE left_table.common_col = right_table.common_col
ORDER BY continent;
-- Subquery inside WHERE (You use it in Semi join / Anti Join)
SELECT left_table.*
FROM left_table
WHERE left_table.some_col <NOT> IN -- Using "NOT" will result in Anti join
( -- subquery start
SELECT another_col
FROM right_table
); -- subquery end
-- Subquery inside SELECT (REMEMBER: This query should only produce a single value)
SELECT
outer_table.id,
(SELECT COUNT(*) FROM inner_table WHERE outer_table.id = inner_table.id) AS new_col_1, -- subquery 1
(SELECT AVG(some_column) FROM inner_table WHERE outer_table.id = inner_table.id) AS new_col_2, -- subquery 2
(SELECT MAX(another_column) FROM inner_table WHERE outer_table.id = inner_table.id) AS new_col_3 -- subquery 3
FROM
outer_table;
-- Using the WITH keyword (creating multiple virtual tables)
WITH
result_table1 AS (
SELECT col1, col2 FROM t1), -- subquery 1
result_table2 AS (
SELECT colx, coly FROM t2) -- subquery 2
SELECT * FROM result_table1, result_table2
WHERE result_table1.col1 <= result_table2.col2
xxxxxxxxxx
USE AdventureWorks2016;
GO
SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Sales.SalesOrderDetail AS OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM Sales.SalesOrderHeader AS Ord;
GO
xxxxxxxxxx
SELECT column_name
FROM table_name1
WHERE VALUE IN
(SELECT column_name
FROM table_name2
WHERE condition)
xxxxxxxxxx
SUBQUERY IS NESTED QUERY INSIDE A SELECT,
INSERT OR UPDATE METHODS. OR INSIDE ANOTHER SUBQUERY
xxxxxxxxxx
-- Example 1: Using a subquery in the WHERE clause
SELECT column_name
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table);
-- Example 2: Using a subquery in the FROM clause
SELECT t1.column_name
FROM (SELECT column_name FROM table_name WHERE condition) AS t1
JOIN table_name2 AS t2 ON t1.column_name = t2.column_name;
-- Example 3: Using a subquery in the SELECT clause
SELECT column_name, (SELECT MAX(value) FROM another_table) AS max_value
FROM table_name;
xxxxxxxxxx
We use subquery in order to get aggregate value in column without grouping data
xxxxxxxxxx
SELECT name, listed_price
FROM paintings
WHERE listed_price > (
SELECT AVG(listed_price)
FROM paintings
);
xxxxxxxxxx
UPDATE Book SET note= 'editore: ' + (SELECT name FROM Publisher WHERE Publisher.ID = Book.publisher)
xxxxxxxxxx
UPDATE dataflair_emp1
SET salary=35000
WHERE emp_id = ( SELECT emp_id
FROM dataflair_emp1
WHERE post='Sr.Manager');
select * from dataflair_emp1;