mySQL - Selecting data from either of two tables with identical schema

tomaugerdotcom used Ask the Experts™
Hey Experts. Here's a good one for you. I'm using sample data in this question to keep the discussion purely about what's possible given mySQL syntax (5.x)

Suppose I have two identical tables A, B such that their column lists are:
A.row_id, A.first_name, A.last_name
B.row_id, B.first_name, B.last_name

In both tables, row_id is the unique Primary key. The data in the two tables would NEVER have the same row_id.

How can I write a SELECT statement that will return a single record from either table?

To illustrate this:
row_id | first_name | last_name
1 | John | Thomas
2 | Jack | London
3 | Mel | Gibson

row_id | first_name | last_name
6 | Joe | Blow
7 | Harry | Dickson
8 | Belly | Johnson

So I'm looking for a select query that if I ask for row_id 6 returns:
6, Joe, Blow
but if I ask for row_id 2 I get
2, Jack, London

Please note: that I don't want a solution where I have to manually select each field and IFNULL them or some such (ie: select ifnull(A.row_id, B.row_id) as rowid, ifnull(A.first_name, B.first_name) as firstname ...etc...) because # fields and fieldnames could change
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT row_id, first_name, last_name FROM A WHERE row_id=the_rownumber
SELECT row_id, first_name, last_name FROM B WHERE row_id=the_rownumber

the_rownumber should be the desired row number.  Since  you said there's no duplicate between the two tables, I just used UNION ALL.  Otherwise you can use UNION DISTINCT to filter out duplicates.


Absofrickenlutely beautiful

UNION is used to combine the result from multiple SELECT  statements into a single result set.

The column names from the first SELECT statement are used as the column names for the results returned.

EXACTLY what I was looking for! Thanks so much - I have never used UNION before and it's precisely what I need.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial