-

UNION VS UNION ALL:

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.
  • UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
  • UNION ALL does not remove duplicates, and it therefore faster than UNION.

Example: If we have two tables, 1) Employee and 2) Customer
  1. Employee table data:
enter image description here
  1. Customer table data:
enter image description here
  1. UNION Example (It removes all duplicate records):
enter image description here
  1. UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):
enter image description here

Comments

Post a Comment

Popular posts from this blog

-
Image
CLUSTERED INDEX VS NON CLUSTERED INDEX Clustered Index Only one clustered index can be there in a table because of the sorted order Sort the records and store them physically according to the order Data retrieval is faster than non-clustered indexes Do not need extra space to store logical structure A primary key by default creates a clustered index on it. Non Clustered Index There can be any number of non-clustered indexes in a table because of the number of unique keys we can create. Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files Data insertion/update is faster than clustered index Use extra space to store logical structure A unique key/keys by dafault created an unclustered index on it. In Depth Explanation: Clustered Technically speaking, a clustered index is a B-Tree data structure where all the rows in a table are stored at the leaf level of the index. In other words, a clustered ind...
Read more
-
Image
General purpose queries: Query 1: Retrieve List of All Database   EXEC  sp_helpdb     Query 2: Display Text of Stored Procedure, Trigger, View   exec  sp_helptext @objname =  'Object_Name'      Query 3: Get All Stored Procedure Relate To Database   SELECT   DISTINCT  o. name , o.xtype      FROM  syscomments c      INNER   JOIN  sysobjects o  ON  c.id=o.id      WHERE  o.xtype= 'P'      To retrieve the View use “V” instead of “P” and for functions use “FN.   Query 4: Get All Stored Procedure Relate To Table   SELECT   DISTINCT  o. name , o.xtype      FROM  syscomments c      INNER   JOIN  sysobjects o  ON  c.id=o.id ...
Read more
-
Dotnet Interview Question: OOPS:  Abstract classes, Interface, Encapsulation, Method hiding, Method overloading, Generics,collection,Delegates,Extension Methods. SQL: Joins, Stored procedures, functions, triggers, transactions, ACID, Normalization. Highest salary query. eliminate duplicate records. Table variables. Temp tables. views. Indexes. ASP.net: state management. page life cycle events. Server.transfer vs Response.Redirect WCF: contracts. security. Design Patterns: singleton. Factory. Abstract Factory. MVC: Routing. Filters. Results. Data Annotations. ADO.net: DataReader. Datasets. Project: Challenges in the current project. Roles & Responsibilities. Number of classes used. Which module you worked in the project. How you have done email notification in MOD(Manager on Duty). How to close the active incidents query. What design you implemented in the project. How do you create incidents query. who ...
Read more