mysql query join/sub-query/union
Say I've two tables - "Table1" and "Table2" in my MySQL database. "id" primary key (auto_increment) in "Table1" is the reference key in "Table2" - "tab_id".
There could be zero or more "Table2" rows for one "Table1" row.
Now I'm trying to do a search on one of the column in "Table2" say "email" column OR on one of the column in "Table1" say "address" and print "Table1" row values.
I see there are 3 possibilities: 1. Join 2. Sub-Query 3. Union
1 Join
SELECT *
FROM Table1 t1, Table t2
WHERE t1.id = t2.tab_id
AND (t1.address like '%str%' OR t2.email like '%str%');
-- This works fine, but when there are no rows in "Table2" relevant to "Table1" .. the JOIN will fail, hence output is in-consistent.
2 Sub-Query
SELECT *
FROM Table1 t1
WHERE t1.address like '%str%'
OR t1.id IN (SELECT t2.tab_id
开发者_运维百科 FROM Table2 t2
WHERE t2.email like '%str%');
-- This works fine, but when there are two manys rows in "Table2" (say 5K) the query goes very slow :(
3 Union
SELECT 'relevant_columns'
FROM Table1 t1, Table t2
WHERE t1.id = t2.tab_id
AND (t1.address like '%str%' OR t2.email like '%str%')
UNION
SELECT 'relevant_columns'
FROM Table1 t1
WHERE t1.address like '%str%'
ORDER BY relevant_column
-- This works fine, may be create a view with a similar UNION, does the job.
Now, my question what is the correct way ... is it okay to call a UNION always?
MySQL Engine: MyISAM
SELECT *
FROM Table1 t1
LEFT JOIN Table t2 ON t2.tab_id = t1.id
WHERE t1.address like '%str%'
OR t2.email like '%str%';
You need to do a
LEFT JOIN
. When you make aFROM
from two tables as you did, it works as anINNER JOIN
(or a CROSS JOIN if there is no WHERE clause), which means that the output shows only rows that have a match in both tables. WithLEFT JOIN
you said that you want all rows from the left table (t1) with the matched row on the right table (t2). If there is no match in t2, thennull
is used.You can use sub-query, but as you can see it is not the best choice
An
UNION
here does not give you any advantage. AnUNION
is useful to merge together datasets with same columns.
Edit
If you have issues with JOIN, because some Table1 rows do not appear, then you need a LEFT JOIN. The fact that takes a long time, is another problem. Those tables are not big at all, so I guess you need to do some index work on those tables.
If you want help about the union
you need to tell me which are those relevant_columns
, because they must have the same number of columns, same type and same sequence.
You might optimize the union
without joins, depending on what you want to output when t2.email
has a match. Here is an example
SELECT t1.id, t1.address, null as email
FROM Table1 t1
WHERE t1.address like '%str%'
union
SELECT t2.tab_id as id, null as address, t2.email
FROM Table t2
WHERE t2.email like '%str%';
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.id = t2.tab_id
WHERE t1.address like '%str%' OR t2.email like '%str%';
精彩评论