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 are instead of triggers?

1103

---

What is mdb stand for?

1067

---

how to shutdown mysql server? : Sql dba

1150

---

What is the difference between an inner and outer join?

1063

---

What is dbo in sql?

1021

---

What is dense_rank?

989

---

What is offset in sql query?

1179

---

Why do we use joins?

1102

---

What are inbuilt functions in sql?

1109

---

What are the sql aggregate functions?

1184

---

how to show all tables with 'mysql'? : Sql dba

1161

---

How to get help at the sql prompt?

1166

---

List the ways to get the count of records in a table?

946

---

What is the difference between delete and truncate statement in sql?

1133

---

What is example of database?

1136

---