sql subqueries

Four ways to introduce a subquery
in a SELECT statement
1. In a WHERE clause as a search condition
2. In a HAVING clause as a search condition
3. In the FROM clause as a table specification
4. In the SELECT clause as a column specification
NOTE: A query with a join typically performs faster than the same query
with a subquerySELECT column_name
 FROM table_name1
WHERE VALUE IN 
(SELECT column_name
FROM table_name2 
WHERE condition)
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-- 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.col2SELECT *
FROM Customers
WHERE age = (
  SELECT MIN(age)
  FROM Customers
);SUBQUERY IS NESTED QUERY INSIDE A SELECT, 
INSERT OR UPDATE METHODS. OR INSIDE ANOTHER SUBQUERY
SELECT name, listed_price
FROM paintings
WHERE listed_price > (
    SELECT AVG(listed_price)
    FROM paintings
);UPDATE Book SET note= 'editore: ' + (SELECT name FROM Publisher WHERE Publisher.ID = Book.publisher)-- 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;UPDATE dataflair_emp1 
SET salary=35000
WHERE emp_id  = ( SELECT emp_id
                    FROM dataflair_emp1
                    WHERE post='Sr.Manager');     
select * from dataflair_emp1;
SELECT incidents.*, sub.incidents AS incidents_that_day
FROM tutorial.sf_crime_incidents_2014_01 incidents
JOIN (
   SELECT date, COUNT(incidnt_num) AS incidents
   FROM tutorial.sf_crime_incidents_2014_01
   GROUP BY 1
) sub ON incidents.date = sub.date
ORDER BY sub.incidents DESC, time;