View over multiple tables containing same columns

Question

I have four tables containing exactly the same columns, and want to create a view over all four so I can query them together.

Is this possible?

(for tedious reasons I cannot/am not permitted to combine them, which would make this irrelevant!)

Answer 1

Use union. Here is explanation

Answer 2

Use the union statement

select * from table1
union 
select * from table2
union
select * from table3

Answer 3

Assuming that in addition to having the same column names, columns of the same contain the same data, you want to create a view that is the union of all those tables.

Something like the following should work, but my SQL is rusty:

(CREATE VIEW view_name AS
(SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT * FROM table3));

Answer 4

You can if you union them (I would suggest including some indicator as to which table each record came from):

select   table1.column1, 1 as TableNumber
from     table1

union

select   table2.column1, 2 as TableNumber
from     table2

.. etc ..

Answer 5

It may be worth noting that you might need to use "union all" to preserve unique rows which may exist in more than one of the tables. A standard union will remove duplicates.

Answer 6

Rather than UNION, use UNION ALL, unless you specifically want duplicate rows to be excluded. UNION on its own takes longer to execute (because of the sort it does to find dupes), and removes duplicate rows.

Answer 7

It is difficult to tell from your query whether you expect the data to be returned based on a UNION, or as a view containing the columns discretely. This obviously has an effect.

Consider the following sample:

TableA
ID  Name   RelatedID
1   John   2
2   Paul   1

TableB
ID  Name   RelatedID
1   Ringo  1
2   George 1

TableC
ID  Name  RelatedID
1   Bob   1

TableD
ID  Name  RelatedID
1   Kate  NULL

Now, run the following query against this:

SELECT ID, Name FROM TableA UNION ALL SELECT ID, Name FROM TableB UNION ALL SELECT ID, Name FROM TableC UNION ALL SELECT ID, Name FROM TableD

This results in the following output:

1 John
2 Paul
1 Ringo
2 George
1 Bob
1 Kate

Is this what you are after? If so, you use a UNION query.

Now, if the effect you are after is to have a discrete view of related data, you may need to do something like this:

SELECT A.ID MasterID, A.Name MasterName, 
       B.ID BandID, B.Name BandName, 
       C.ID BlackadderID, C.Name BlackadderName
       D.ID BlackadderRealID, D.Name BlackadderRealName
FROM
  TableA A
INNER JOIN
  TableB B
ON
  A.RelatedID = B.ID
INNER JOIN
  TableC C
ON
  B.RelatedID = C.ID
INNER JOIN
  TableD D
ON
  C.RelatedID = D.ID

This will result in the following view of the data:

MasterID  MasterName  BandID  BandName BlackAdderID BlackAdderName  BlackadderRealID  BlackadderRealName
1         John        2       George   1            Bob             1                 Kate
2         Paul        1       Ringo    1            Bob             1                 Kate