Using subquery result for inner join

Question

I have two tables. i want to filter out rows from table class then take that filtered table and join it with books to compare two columns of this new table (orderid from class table and order_id from books table). whenever these two columns matches , i want to select that row. i have tried this

query = """ select col1 , col2 , col3 from class 
INNER JOIN books
ON class.order_id = books.orderid 
IN (SELECT orderid from books where name=%s and lastname=%s ); """

my tables have thousands of rows so it takes very long to execute. is there any better solution?

Answer 1

I assume that col1, col2, col3 comes from table calss then you can use exists

select col1 , col2 , col3 
from class c
WHERE exists 
(
  SELECT 
    orderid 
  from books b
  where c.order_id = b.orderid 
  and name=%s 
  and lastname=%s 
)

Other option is to select orderId in sub-query

select col1 , col2 , col3 
from class c
INNER JOIN (SELECT orderid from books where name=%s and lastname=%s) b
ON c.order_id = b.orderid 

Otherwise you can use your original query where you are missing where

select col1 , col2 , col3 
from class 
INNER JOIN books
ON class.order_id = books.orderid 
WHERE orderid IN (
  SELECT orderid from books where name=%s and lastname=%s 
)