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? :(

Answer 1

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).

Answer 2

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

Answer 3

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.

Answer 4

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) 

Answer 5

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
}