Answer Posted / gaurav arora

The answer-1 is very much correct. Let me clear it with
example:

Union vs. Union All
In simple we can say that
1. union is used to select distinct values from two
tables,where as union all is used to select all values
including duplicates from the tables.
2. The UNION operator allows you to combine the results of
two or more SELECT statements into a single result set. The
result sets combined using UNION must all have the same
structure. They must have the same number of columns, and
the corresponding result set columns must have compatible
data types.By default, the UNION operator removes duplicate
rows from the result set. If you use UNION ALL, all rows
are included in the results and duplicates are not removed.

Lets consider following examples:

1. UNION
Select * from dbo.checkDuplicate
Union --it will leave the duplicate rows
Select * from dbo.checkDuplicate

The above querry will retrieve all rows from checkduplicate
table except duplicate entries.

2. UNION ALL
Select * from dbo.checkDuplicate
Union --it will select all rows including duplicates
Select * from dbo.checkDuplicate

The above querry will select all rows from checkduplicate
table including duplicate entries.

Note: One can count the number of rows using following
statement:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID
('checkDuplicate') AND indid < 2

To get above used table please run following querry:
CREATE TABLE dbo.checkDuplicate
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Shuby','Arora')
INSERT INTO dbo.checkDuplicate VALUES(3, 'Amit','Gupta')
INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES
(5, 'Neelima','Malhotra')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Meghna','Arora')

Thanks,
Gaurav Arora
http://stuff4mdesktop.blogspot.com/

What is sqlca in powerbuilder?

953

---

how can we destroy the session, how can we unset the variable of a session? : Sql dba

932

---

What is bulk compiling in pl/sql.?

1057

---

What is date functions?

958

---

What does a pl/sql package consist of?

927

---

What is file based approach?

874

---

How can you tell the difference between an index and a view?

868

---

Explain how you can copy a file to file content and file to pl/sql table in advance pl/sql?

954

---

What are variables in pl sql?

1090

---

What is an exception in PL/SQL? What are the two types of exceptions?

1189

---

What is the difference between a primary key and a clustered index?

926

---

Which data dictionary views have the information on the triggers that are available in the database?

1179

---

What is a crud api?

899

---

Under what condition it is possible to have a page level lock and row lock at the same time for a query? : Transact sql

925

---

How does cross join work in sql?

955

---