What kind of SQL join is this?

Question

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

Answer 1

I think you refer to the UNION operation.

Answer 2

You will want to use the UNION keyword

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

Answer 3

You'll just want to do a union

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

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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".

Answer 9

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

Answer 10

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.