How to join two tables in one query?

Question

How do I get table C from table A and table B in one select statement? Thanks.

Table A -
Product, Date, Price 
P1, 2018-01-01, 10
P2, 2018-01-02, 20

Table B - 
Date
2018-01-01
2018-01-02
2018-01-03
2018-01-04

Table C
Product, Date, Price
P1, 2018-01-01, 10 
P1, 2018-01-02, null
P1, 2018-01-03, null
P1, 2018-01-04, null
P2, 2018-01-01, null
P2, 2018-01-02, 20 
P2, 2018-01-03, null
P2, 2018-01-04, null

Answer 1

CROSS JOIN will create a full cross product between the tables. Then you can fill in the Price column when the dates match.

SELECT
  Product,
  TableB.Date,
  IF(TableA.Date = TableB.Date, Price, NULL END) AS Price
FROM TableA
CROSS JOIN TableB
ORDER BY Product, Date

DEMO

Answer 2

since you need all the rows from table A with all the dates in tables B, you need to use FULL OUTER JOIN:

SELECT * FROM Table_A a
FULL OUTER JOIN Table_B b
ON a.date = b.date 

but since, there is no full outer join in My sql you can emulate it by using this post: How to do a FULL OUTER JOIN in MySQL? in MYSQL:

SELECT * 
FROM Table_A a
LEFT JOIN Table_B b ON a.date=B.date
UNION ALL
SELECT * 
FROM Table_A a 
RIGHT JOIN table_B b
ON a.date=b.date

Answer 3

select ta.product , tb.Date , ta.Price from TableA ta, Table