EXPERT RESPONSE
When you join separate tables together the way you have done, two things
can happen, and both of them are usually bad.
If you leave out the join conditions,
as you have done, the effect is a cross join, where you get all rows
of every table matched with every row of all other tables. So if your four tables
have 12, 15, 20, and 25 rows respectively, then a cross join returns 90,000 rows.
Even if you restrict those 90,000 by specifying that the email must
exist in one of the four fields, as you've done, there will probably still be far
too many rows.
If you do include join conditions, to match rows based
on some common column, like email in your case, then chances are you will get
no rows back at all, because that would mean that the same email would have to
exist in all four tables, whereas what you want is to find it if it exists in
any of them, not all of them.
Whenever you have similar but separate tables, you should think immediately of
UNION. UNION allows you to search tables separately but return everything in
one query. If you wish to know which table a row came from, put an "identifying"
column into the results.
select 'fk' as identifier
, fk.email as email
, fk.kod as kod
from prop_buy fk
where fk.email = value
UNION ALL
select 'fs'
, fs.email
, fs.kod
from prop_sell fs
where fs.email = value
UNION ALL
select 'L '
, L.epost
, L.kod
from LongTerm L
where L.email = value
UNION ALL
select 'sh'
, sh.kod
, sh.email
from ShortTerm sh
where sh.email = value
order
by 1
UNION produces a result set with column names and datatypes taken
from the first subselect. Use UNION ALL to avoid an unnecessary
sort to remove duplicate rows when there cannot be duplicate rows,
such as in this example where the identifier column is different
in each subselect. If you want the results sorted,
you can have only one ORDER BY clause, and it goes at the end;
use ordinal numbers to specify columns in the result set
(not all database allow you to use column aliases in the ORDER BY clause).
|
