0

I have a question concerning a more efficient sql query which will select records from driving table, which is missing in second tables. Tables have more than 23k rows, and join takes too long.

Is there any way to make it faster? According to me, the following query loops through whole table

I used 

SELECT * FROM orders
JOIN orderID USING(Order_id)
WHERE orderID != Order_id
asontu
  • 4,548
  • 1
  • 21
  • 29
DRastislav
  • 1,892
  • 3
  • 26
  • 40

2 Answers2

1

You want to use a left join. It would look something like this:

SELECT o.*
FROM orders o LEFT JOIN 
     SecondTable st
     ON o.orderID = st.Order_id
WHERE st.Order_id IS NULL;

I find it unlikely that the second table is called Order_id, so I've called it SecondTable in this query. You probably don't need the columns from the non-existent record, so I've limited them to columns from the first table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

You are generating a cartesian product of both tables. What you want is to to a regular LEFT JOIN with an appropriate where clause:

SELECT * FROM orders
JOIN orderID USING(Order_id)
ON orders.orderID = orderID.Order_id
WHERE orderID.Order_id IS NULL;
lnrdo
  • 396
  • 1
  • 13