SQL - compares column values from two different table-CodePudding

I am new to SQL servers and would like to how should I approach the following case:

I have two tables:

1st table has this month's orders and has the following data

Product	Order
First	2000
Second	3000

... and 10000 rows

and 2nd table has the expected orders for the upcoming month and has the following data:

Product	Order
First	3000
Second	1000

... and 12000 rows.

Now, I want to see the products in the 2nd table where expected orders are more than the values in the Order column in the 1st table. For eg. the output should in our case should be

| Product |
| First |

Can you please help me how should I approach this as the column names are similar?

CodePudding user response：

you can join them by product name using aliases for example and compare their orders

select eo.product, eo.order from current_orders co 
    join exp_orders eo on co.product = eo.product 
where eo.order < co.order;

CodePudding user response：

You have to use the inner join command, the sintax is like this:

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Inner join will give you the rows that are in both tables where the condition on is true, then you add AND for the other condition that you want, something like this:

SELECT Product,Orders FROM table2 INNER JOIN table1 ON table2.Product = table1.Product AND table2.Product > table1.Product ;

CodePudding user response：

Check the required criteria with exists:

select *
from table2 t2
where exists (
  select * from table1 t1
  where t1.product = t2.product
    and t2.order > t1.order
);