SQL join tables with placing null to not existing values

Question

I'm trying to find which market doesn't have which product according to visit date. In order to do this I thought if I give all products to all markets there will be null dates because there will be no visit for that product for the market. To see all products in the market I wrote a query :

SELECT id, p.ProductName FROM atb_markets
CROSS JOIN
(SELECT StokAd FROM atb_products) p

and I got this kind of a view:

 MarketId   productName
    1         a
    1         b
    1         c
    1         d
    1         e
    1         f
    2         a
    2         b
    2         c
    2         d
    2         e
    .         .
    .         .

By the way these all are different tables (atb_markets, atb_products) and dates are saving on the outformmobiledata table. This table holds each record for the markets. marketId, productId and date is holding on this table (date information comes from mobile devices). Because of I need productName I need atb_products table also.

Finally I need this view by using these three tables. If I get null values I can know which products don't exists on a market.

And this is the view I need:

date        MarketId   productName
01.12.2013     1         a
11.12.2013     1         b
NULL           1         c
04.12.2013     1         d
20.12.2013     1         e
05.12.2013     1         f
06.12.2013     2         a
NULL           2         b
NULL           2         c
12.12.2013     2         d
NULL           2         e
.              .         .
.              .         .

**As you see in the second table market_number1 never had product c

Answer 1

Just use LEFT OUTER JOIN for outformmobiledata table:

SELECT d.date, m.id, p.ProductName
  FROM atb_markets m
  CROSS JOIN atb_products p
  LEFT OUTER JOIN outformmobiledata d
    ON d.marketId = m.id
    AND d.productId = p.id
ORDER BY m.id, p.ProductName