How can i join two tables after filtering

Question

How can i join two tables after filtering each table with some condition and on getting the result of filter, join both the results.

Tried code but showing syntax error

SELECT * 
FROM Table1 
WHERE flag1 = 0  
JOIN SELECT * 
     FROM TABLE2 
     where flag2 = 0 on Table1.email = TABLE2.email

`select ... from t1 join t2 on ... where...`, i.e. only one WHERE per SELECT. — jarlh, Dec 06 '17 at 09:55
@jarlh Sorry but I don't want this. I have some timecomplexity issue by using join on whole table. — Aditya Srivastava, Dec 06 '17 at 09:56
Possible duplicate of [JOIN two SELECT statement results](https://stackoverflow.com/questions/10538539/join-two-select-statement-results) — SharpKnight, Dec 06 '17 at 09:58
What's wrong with my advice? That's the way it's supposed to be done. — jarlh, Dec 06 '17 at 09:58
@AdityaSrivastava - It's that simple. Put the where after the on clause. DBs are clever now. They'll see what to filter and then join — Gurwinder Singh, Dec 06 '17 at 10:00

score 2 · Answer 1 · answered Dec 06 '17 at 10:04

There may not be enough (RAM/cache) space to save your filtered results and then join them together. It can be slower than you expect.

*technically you can, but not that efficient.

SELECT * 
FROM (
    SELECT * FROM Table1 where flag1 = 0
) A
JOIN (
    SELECT * FROM TABLE2 where flag2 = 0
) B 
ON A.orderlist_email = B.email;

however, I'd recommend just join them first, and where everything later. MySQL (or whatever sql engine) will (usually) do a fine job optimizing it for you.

SELECT * 
FROM Table1
JOIN TABLE2 
ON A.orderlist_email = B.email
WHERE Table1.flag1 = 0 
AND TABLE2.flag2 = 0;

score 2 · Accepted Answer · answered Dec 06 '17 at 10:05

Consider the two Select Statements as SUBQUERIES, and the results of these two subqueries as two different tables and then try joining.

SELECT * FROM 
(SELECT * FROM Table1 where flag1 = 0) Table1 
JOIN 
(SELECT * FROM TABLE2 where flag2 = 0) Table2 
on Table1.orderlist_email = TABLE2.email

Naveed Ramzan · Answer 3 · 2017-12-06T10:11:05.223

0

SELECT *  
FROM Table1 as t1
join Table2 as t2 on t1.orderlist_email = t2.email
where flag1 = 0 
and flag2 = 0

Correct join will be like this.

edited Dec 06 '17 at 10:11

answered Dec 06 '17 at 10:02

Naveed Ramzan

3,565
3
25
30

Where is flag2? – Ilyes Dec 06 '17 at 10:03
@Sami: majorly answer was related to join and where clause. Anyway I updated the answer – Naveed Ramzan Dec 06 '17 at 10:11

score 0 · Answer 4 · answered Dec 06 '17 at 10:03

Unless I'm missing something, this ought to be pretty straightforward. Pseudo-code, but you can solve this a few ways...

SELECT 
   * 
FROM 
   Table1 
   JOIN TABLE2 ON Table1.orderlist_email = TABLE2.email
   Table1.flag1 = 0 
   AND TABLE2.flag2 = 0

Alternatively, you can add the WHERE conditions to the JOIN like so:

SELECT 
   * 
FROM 
   Table1 
   JOIN TABLE2 ON Table1.orderlist_email = TABLE2.email
   AND Table1.flag1 = 0 
   AND TABLE2.flag2 = 0

I think your concern is whether or not this means the engine will still perform a full scan of both tables, rather than sticking with a "pre-filtered" set it will attempt to join. Modern engines should be clever enough to figure this stuff out, but based on the complexity of your query, your mileage might vary.

How can i join two tables after filtering

4 Answers4