How to select rows that are not in table B but they're in table A ( left join )

Question

I'm using DB2 to develop an app,

I have the tables products, price and cost, these tables store products data at this way:

As you can see, for this example it is possible to have products with a price but with no cost assigned (so the row doesn't exist, it is not like a product can have cost = null).

What I want to do is to get all the ids of the products whose are assigned to a price but not to a cost, so the result should be:

I think I can't do an outer left or right join since in order to do that I'd need both tables (price and cost) to have the ids present on both of them.

I'm not pretty sure what version of DB2 I'm using (I'm pre-configured software to interact with DB2) so it would help me if you use SQL Server syntax since it is pretty alike DB2.

Could anybody help me please? :(

It is a good question. Your question has been properly edited. It is clear. You included an example. Keep it up @Rodolfo BocaneGra — Pie, Jul 21 '19 at 08:08
You're not new. You should **not** be posting information as images since that prevents others from searching for it and from using copy/paste to help research/resolve your question. In addition, your question is not related to sql server - removed that tag. SQL Server and DB2 use different sql dialects - except for the simplest of queries you will only confuse things by attempting to use tsql. — SMor, Jul 21 '19 at 13:10

score 4 · Accepted Answer · answered Jul 21 '19 at 02:22

4

This is a simple left join/where:

select p.id
from price p left join
     cost c
     on p.id = c.id
where c.id is null;

You can also use not exists (and not in, but I don't recommend that when using subqueries).

answered Jul 21 '19 at 02:22

Gordon Linoff

1,242,037
58
646
786

Very nice. Works perfectly. – zumzum Feb 03 '21 at 23:18

score 0 · Answer 2 · answered Jul 21 '19 at 11:36

You can use left join for this situation :

SELECT
    select_list
FROM
    T1
LEFT JOIN T2 ON
    join_predicate;

sample with where clause:

SELECT Customers.CustomerName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.CustomerID is null

More information on Sql Server left join

score 0 · Answer 3 · answered Jul 21 '19 at 12:15

0

This case is the reason why the statement EXISTS and NOT EXISTS exist in SQL:

select p.id from price p 
where not exists (
  select 1 from cost
  where id = p.id
)

It is self explanatory and in such simple cases more efficient than other solutions.

answered Jul 21 '19 at 12:15

forpas

160,666
10
38
76

score 0 · Answer 4 · answered Jul 21 '19 at 12:33

0

In DB2 , It would be :

SELECT product.id
FROM product 
INNER JOIN price ON product.id= price.fkProductID
WHERE NOT EXISTS (SELECT id FROM cost
                    WHERE cost.fkProductID = product.id)

answered Jul 21 '19 at 12:33

Roxana Sh

294
1
3
14

score 0 · Answer 5 · answered Apr 19 '22 at 22:10

Since the question is generic and I came here looking for further explanation on joins using LINQ I thought I would post my solution here. I used the post at LEFT OUTER JOIN in LINQ that had a detailed example and I modified it to fit this one and then entered it into LINQPad.

void Main()
{
    var Products = new List<Tuple<int, string>>
    {
        new Tuple<int,string>(1, "Product A"),
        new Tuple<int,string>(2, "Product B"),
        new Tuple<int,string>(3, "Product C"),
        new Tuple<int,string>(4, "Product D"),
        new Tuple<int,string>(5, "Product E")       
    };

    var Prices = new List<System.Tuple<int, decimal>>
    {
        new Tuple<int,decimal>(1, 100),
        new Tuple<int,decimal>(2, 150),
        new Tuple<int,decimal>(3, 20),
        new Tuple<int,decimal>(4, 90),
        new Tuple<int,decimal>(5, 120)
    };

    var Costs = new List<System.Tuple<int, decimal>>
    {
        new Tuple<int,decimal>(1, 50),
        new Tuple<int,decimal>(2, 75)       
    };      
    
    var query2 = 
        from products in Products
        from prices in Prices
            .Where(price => price.Item1 == products.Item1)
            .DefaultIfEmpty() // <== makes join a left join
        from costs in Costs
            .Where(cost => cost.Item1 == prices.Item1)
            .DefaultIfEmpty() // <== makes join a left join
            
        select new
        {
            ID = products.Item1,
            Product = products.Item2,
            Prices = prices.Item2,              
            Costs = costs != null ? costs.Item2.ToString("C2") : "No Cost"
        };
            
    var xy = query2.ToList();
    
    xy.Dump();  // Dump() <== Linqpad command to create output
}

How to select rows that are not in table B but they're in table A ( left join )

5 Answers5