CODEWITHSUNDEEP
sqljoinunion
mvrak
mvrak

Reputation: 501

What kind of SQL join is this?

Say for some reason I have employees in two separate tables, employee1 and employee2

I just want to add them together, as if they are stacked on top of each other.

something like:

select all from employee1 and employee2 where name = bubba

i know im generalizing, this will be in postgres eventually so if there are any specifics there i should watch for thanks

Upvotes: 0

Views: 306

Answers (10)

James B
James B

Reputation: 3750

For what it's worth, union all is quicker to execute as it doesn't have to sort to get rid of duplicates in the two sets.

Upvotes: 0

Roatin Marth
Roatin Marth

Reputation: 24105

As others have mentioned, you want UNION. However if you truely want the results stacked, you should use UNION ALL. UNION will remove the dupes, UNION ALL with include them. See http://www.postgresql.org/docs/8.2/interactive/queries-union.html

Upvotes: 0

Rob F
Rob F

Reputation: 334

What you want is a "union all":

select * from employee1
union all
select * from employee2;

Column types and order must match, or you'll need to provide column lists rather than "*" in the select list. A "where" clause can be added to either or both "select" statements.

Without "all", any duplicate rows between the two queries will be collapsed into a single row. If that's what you want instead, just remove "all".

Upvotes: 1

Gratzy
Gratzy

Reputation: 9389

Its a union I guess

Select * from employee 1 where name = 'bubba'

union

select * from employee2 where name = 'bubba'

Use union all if you want duplicates as well.

Upvotes: 1

DVK
DVK

Reputation: 129433

In most databases what you are requesting is called a UNION and written like this:

select all from employee1 where name = bubba

UNION

select all from employee2 where name = bubba

This comes from Relational Algebra's "union" operator, one of its primitives.

Please note that UNION follows set unions, namely, it will, for any rows which are duplicate between E1 and E2 tables, only select ONE copy of the row. If you wish to select all copies, use "UNION ALL" operator.

Upvotes: 2

dnagirl
dnagirl

Reputation: 20456

SELECT field1, field2, field2 FROM tableA WHERE field1='x'
UNION
SELECT field1, field2, field2 FROM tableB WHERE field1='x'

Use UNION ALL if you want every record, even repeats.

Upvotes: 11

AdamW
AdamW

Reputation: 1061

If the table have the same schema then

SELECT * FROM employee1 UNION SELECT * FROM employee2

Both tables must have the same number of columns and the columns must be of a similar type.

Upvotes: 1

CaffGeek
CaffGeek

Reputation: 22054

You'll just want to do a union

select * from Employee1 where name = 'bubba'
union
select * from Employee2 where name = 'bubba'

Upvotes: 4

Bob
Bob

Reputation: 99764

You will want to use the UNION keyword

select * from employee1 where name = 'bubba'
union
select * from employee2 where name = 'bubba'

Upvotes: 3

KB22
KB22

Reputation: 6969

I think you refer to the UNION operation.

Upvotes: 1

Related Questions