Data Engineering Technical Blog

 SQL Server set operation set operation allows us combine or select common data from more 2 tables/ queries. If you want to get data from more than two tables then you will have to repeat set operator between each query. There are basically 3 types of set operations like  Union  Union all (special case A + B) Intersection Except (Minus)  For demo we will use the below query to create a table and use it throughout out examples CREATE TABLE Product1 (ProductID INT, ProductName VARCHAR(15))  CREATE TABLE Product2 (ProductID INT, ProductName VARCHAR(15))  INSERT INTO Product1 VALUES (1,'Pen'),(3,'Notebook'),(4,'Pin'),(6,'Punch'),(7,'Glue') INSERT INTO Product2 VALUES (1,'Pen'),(2,'Stapler'),(4,'Pin'),(8,'Book'),(9,'Glitter') UNION union allows us to combine data from two different queries/tables. If the data is common between 1st and 2nd query then it gives distinct data. The output of union is sorted.  A = {1,...

What is a correlated subquery ? Correlated subquery is a sql query where the subquery portion depends on the main/outer query to interpret its result.  For example if you want to get 2nd max SalesAmt you can use the correlated subquery as below. Create table #temp ( Country VARCHAR(10), [State] VARCHAR(10), SalesAmt Money ) INSERT INTO #temp VALUES('India','Karnataka',50000),('India','Andhra',30000),('India','Telangana',15000), ('USA','Texas',50000),('USA','NY',32000),('USA','Redmond',15000), ('UK','London',50000),('UK','Reading',30100),('UK','Edinburg',15000) SELECT * FROM #temp T WHERE T.SalesAmt = ( SELECT MAX(SalesAmt) Amt FROM #temp A WHERE SalesAmt < (SELECT MAX(SalesAmt) FROM #temp WHERE A.Country = Country) AND  A.Country = T.Country) There are easier methods to get 2nd max like using Ranking functions. You can also combine Gro...