I need help regarding JOIN query in mysql

Question

I have started learning MySQL and I'm having a problem with JOIN.

I have two tables: purchase and sales

purchase
--------------
p_id  date          p_cost   p_quantity
---------------------------------------
1     2014-03-21       100       5
2     2014-03-21        20       2


sales
--------------
s_id  date          s_cost   s_quantity
---------------------------------------
1     2014-03-21       90       9
2     2014-03-22       20       2

I want these two tables to be joined where purchase.date=sales.date to get one of the following results:

Option 1:

p_id  date         p_cost  p_quantity   s_id   date        s_cost   s_quantity
------------------------------------------------------------------------------
1     2014-03-21    100       5         1      2014-03-21   90       9 
2     2014-03-21     20       2         NULL   NULL         NULL     NULL 
NULL  NULL         NULL      NULL       2      2014-03-22   20       2

Option 2:

p_id  date         p_cost  p_quantity   s_id   date        s_cost   s_quantity
------------------------------------------------------------------------------
1     2014-03-21    100       5         NULL   NULL         NULL     NULL
2     2014-03-21     20       2         1      2014-03-21   90       9
NULL  NULL         NULL      NULL       2      2014-03-22   20       2

the main problem lies in the 2nd row of the first result. I don't want the values
2014-03-21, 90, 9 again in row 2... I want NULL instead.

I don't know whether it is possible to do this. It would be kind enough if anyone helps me out.

I tried using left join

SELECT * FROM sales LEFT JOIN purchase ON sales.date = purchase.date

output:

s_id date s_cost s_quantity p_id date p_cost p_quantity 1 2014-03-21 90 9 1 2014-03-21 100 5 1 2014-03-21 90 9 2 2014-03-21 20 2 2 2014-03-22 20 2 NULL NULL NULL NULL

but I want 1st 4 values of 2nd row to be NULL

Looks like a simple problem but I do not understand a thing... Make it more readable. Give some SQL, give the expected result. — Pellared, Mar 21 '14 at 18:08

score 2 · Answer 1 · answered Mar 21 '14 at 18:48

Since there are no common table expressions or full outer joins to work with, the query will have some duplication and instead need to use a left join unioned with a right join;

SELECT p_id, p.date p_date, p_cost, p_quantity,
       s_id, s.date s_date, s_cost, s_quantity
FROM (
  SELECT *,(SELECT COUNT(*) FROM purchase p1 
            WHERE p1.date=p.date AND p1.p_id<p.p_id) rn FROM purchase p
) p LEFT JOIN (
  SELECT *,(SELECT COUNT(*) FROM sales s1 
            WHERE s1.date=s.date AND s1.s_id<s.s_id) rn FROM sales s
) s
ON s.date=p.date AND s.rn=p.rn

UNION

SELECT p_id, p.date p_date, p_cost, p_quantity,
       s_id, s.date s_date, s_cost, s_quantity
FROM (
  SELECT *,(SELECT COUNT(*) FROM purchase p1 
            WHERE p1.date=p.date AND p1.p_id<p.p_id) rn FROM purchase p
) p RIGHT JOIN (
  SELECT *,(SELECT COUNT(*) FROM sales s1 
            WHERE s1.date=s.date AND s1.s_id<s.s_id) rn FROM sales s
) s
ON s.date=p.date AND s.rn=p.rn

An SQLfiddle to test with.

awesome, yes this is the one! – KrazzyNefarious Mar 21 '14 at 19:00 — KrazzyNefarious, Mar 21 '14 at 19:00

score 1 · Answer 2 · edited May 23 '17 at 10:26

In a general sense, what you're looking for is called a FULL OUTER JOIN, which is not directly available in MySQL. Instead you only get LEFT JOIN and RIGHT JOIN, which you can UNION together to get essentially the same result. For a very thorough discussion on this subject, see Full Outer Join in MySQL.

If you need help understanding the different ways to JOIN a table, I recommend A Visual Explanation of SQL Joins.

The way this is different from a regular FULL OUTER JOIN is that you're only including any particular row from either table at most once in the JOIN result. The problem being, if you have one purchase record and two sales records on a particular day, which sales record is the purchase record associated with? What is the relationship you're trying to represent between these two tables?

It doesn't sound like there's any particular relationship between purchase and sales records, except that some of them happened to take place on the same day. In which case, you're using the wrong tool for the job. If all you want to do is display these tables side by side and line the rows up by date, you don't need a JOIN at all. Instead, you should SELECT each table separately and do your formatting with some other tool (or manually).

@farha I would be curious to know the size of the tables you're working with, and how long Joachim's query takes to finish. It's a neat trick, but the execution plan is pretty scary. — Air, Mar 21 '14 at 19:05
@AirThomas actually i was planning on doing something like this for my project.but i'm sure i need to change my idea. — farha, Mar 22 '14 at 05:29

spencer7593 · Answer 3 · 2014-03-21T20:15:55.253

Here's another way to get the same result, but the EXPLAIN for this is horrendous; and performance with large sets is going to be atrocious.

This is essentially two queries UNIONed together. The first query is essentially "purchase LEFT JOIN sales", the second query is essentially "sales ANTI JOIN purchase".

Because there is no foreign key relationship between the two tables, other than rows matching on date, we have to "invent" a key we can join on; we use user variables to assign ascending integer values to each row within a given date, so we can match row 1 from purchase to row 1 from sales, etc.

I wouldn't normally generate this type of result using SQL; it's not a typical JOIN operation, in the sense of how we traditionally join tables.

But, if I had to produce the specified resultset using MySQL, I would do it like this:

SELECT p.p_id
     , p.p_date
     , p.p_cost
     , p.p_quantity
     , s.s_id
     , s.s_date
     , s.s_cost
     , s.s_quantity
  FROM ( SELECT @pl_i := IF(pl.date = @pl_prev_date,@pl_i+1,1) AS i
              , @pl_prev_date := pl.date AS p_date
              , pl.p_id
              , pl.p_cost
              , pl.p_quantity
           FROM purchase pl
           JOIN ( SELECT @pl_i := 0, @pl_prev_date := NULL ) pld
          ORDER BY pl.date, pl.p_id
       ) p
  LEFT
  JOIN ( SELECT @sr_i := IF(sr.date = @sr_prev_date,@sr_i+1,1) AS i
              , @sr_prev_date := sr.date AS s_date
              , sr.s_id
              , sr.s_cost
              , sr.s_quantity
           FROM sales sr
           JOIN ( SELECT @sr_i := 0, @sr_prev_date := NULL ) srd
          ORDER BY sr.date, sr.s_id
       ) s
    ON s.s_date = p.p_date
   AND s.i = p.i
 UNION ALL
SELECT p.p_id
     , p.p_date
     , p.p_cost
     , p.p_quantity
     , s.s_id
     , s.s_date
     , s.s_cost
     , s.s_quantity
  FROM ( SELECT @sl_i := IF(sl.date = @sl_prev_date,@sl_i+1,1) AS i
              , @sl_prev_date := sl.date AS s_date
              , sl.s_id
              , sl.s_cost
              , sl.s_quantity
           FROM sales sl
           JOIN ( SELECT @sl_i := 0, @sl_prev_date := NULL ) sld
          ORDER BY sl.date, sl.s_id
       ) s
  LEFT
  JOIN ( SELECT @pr_i := IF(pr.date = @pr_prev_date,@pr_i+1,1) AS i
              , @pr_prev_date := pr.date AS p_date
              , pr.p_id
              , pr.p_cost
              , pr.p_quantity
           FROM purchase pr
           JOIN ( SELECT @pr_i := 0, @pr_prev_date := NULL ) prd
          ORDER BY pr.date, pr.p_id
       ) p
    ON p.p_date = s.s_date
   AND p.i = s.i
 WHERE p.p_date IS NULL
 ORDER BY COALESCE(p_date,s_date),COALESCE(p_id,s_id)

I need help regarding JOIN query in mysql

3 Answers3