1

Let there are 2 tables. To query the rows, which have the same IDs, you have to do this:

SELECT * FROM Table1 A, Table2 B WHERE A.id = B.id

Now let the tables be merged into one global table, with an added ex-table column. So, query

SELECT * FROM Table1

now looks like:

SELECT * FROM GlobalTable WHERE tableId = 1

But how the first query should look now?

SELECT * FROM Table1 A, Table2 B WHERE A.id = B.id

?

noober
  • 4,819
  • 12
  • 49
  • 85
  • You should never use implicit joins, they are a poor programming practice which often creates subtle bugs in your queries. They were replaced almost 20 years ago for good reason. – HLGEM Jul 11 '11 at 14:42

1 Answers1

2

One table should store one entity. There is no such thing as a "one true lookup table" or "global table". Nor should you consider an EAV. This question assumes all your tables have the same layout...

However, I look forward to more rep later when it doesn't work properly so...

You should use explicit JOINs to separate filter and join conditions

Select *
from
   GlobalTable A
   JOIN
   GlobalTable B ON A.id = B.id
WHERE
    A.tableId = 1 AND B.tableId = 2

If you need to do an OUTER JOIN, then you can write this

Select *
from
   (SELECT * FROM GlobalTable WHERE tableId = 1) A
   LEFT JOIN
   (SELECT * FROM GlobalTable WHERE tableId = 2) B ON A.id = B.id

I'd suggest using an indexed view though to persist "tableA" and "tableB" as separate objects to avoid this continual filtering. Or don't merge them...

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Could you please give some clue, why the answer below shouldn't work and JOIN should? – noober Jul 11 '11 at 12:20
  • @noober: works the same, but JOIN is better. See http://stackoverflow.com/questions/5654278/sql-join-is-there-a-difference-between-using-on-or-where/5654338#5654338 – gbn Jul 11 '11 at 12:24