right join versus left join

Question

In this case, a left join is the same as a right join?

mysql> 
mysql> 
mysql> use usenet;show tables;describe ARTICLE;describe NEWSGROUP;
Database changed
+------------------+
| Tables_in_usenet |
+------------------+
| ARTICLE          |
| NEWSGROUP        |
+------------------+
2 rows in set (0.00 sec)

+---------------+------------+------+-----+---------+----------------+
| Field         | Type       | Null | Key | Default | Extra          |
+---------------+------------+------+-----+---------+----------------+
| ID            | bigint(20) | NO   | PRI | NULL    | auto_increment |
| MESSAGENUMBER | int(11)    | YES  |     | NULL    |                |
| NEWSGROUP_ID  | bigint(20) | YES  | MUL | NULL    |                |
+---------------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| ID        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| NEWSGROUP | varchar(255) | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> 
mysql> select * from ARTICLE right join NEWSGROUP on ARTICLE.NEWSGROUP_ID=NEWSGROUP.ID;
+------+---------------+--------------+----+-------------------------------+
| ID   | MESSAGENUMBER | NEWSGROUP_ID | ID | NEWSGROUP                     |
+------+---------------+--------------+----+-------------------------------+
|    1 |             4 |            1 |  1 | gwene.com.androidcentral      |
|    2 |             5 |            1 |  1 | gwene.com.androidcentral      |
|    3 |             6 |            1 |  1 | gwene.com.androidcentral      |
|    4 |             7 |            1 |  1 | gwene.com.androidcentral      |
|    5 |             8 |            1 |  1 | gwene.com.androidcentral      |
|    6 |             9 |            1 |  1 | gwene.com.androidcentral      |
|    7 |            10 |            1 |  1 | gwene.com.androidcentral      |
|    8 |            11 |            1 |  1 | gwene.com.androidcentral      |
|    9 |             4 |            2 |  2 | gwene.com.blogspot.emacsworld |
|   10 |             4 |            3 |  3 | gwene.com.blogspot.googlecode |
|   11 |             5 |            3 |  3 | gwene.com.blogspot.googlecode |
|   12 |             6 |            3 |  3 | gwene.com.blogspot.googlecode |
|   13 |             7 |            3 |  3 | gwene.com.blogspot.googlecode |
|   14 |             8 |            3 |  3 | gwene.com.blogspot.googlecode |
|   15 |             9 |            3 |  3 | gwene.com.blogspot.googlecode |
|   16 |            10 |            3 |  3 | gwene.com.blogspot.googlecode |
|   17 |            11 |            3 |  3 | gwene.com.blogspot.googlecode |
|   18 |             4 |            4 |  4 | gwene.com.economist           |
|   19 |             5 |            4 |  4 | gwene.com.economist           |
|   20 |             6 |            4 |  4 | gwene.com.economist           |
|   21 |             7 |            4 |  4 | gwene.com.economist           |
|   22 |             8 |            4 |  4 | gwene.com.economist           |
|   23 |             9 |            4 |  4 | gwene.com.economist           |
|   24 |            10 |            4 |  4 | gwene.com.economist           |
|   25 |            11 |            4 |  4 | gwene.com.economist           |
+------+---------------+--------------+----+-------------------------------+
25 rows in set (0.00 sec)

mysql> 
mysql> select * from ARTICLE left join NEWSGROUP on ARTICLE.NEWSGROUP_ID=NEWSGROUP.ID;
+----+---------------+--------------+------+-------------------------------+
| ID | MESSAGENUMBER | NEWSGROUP_ID | ID   | NEWSGROUP                     |
+----+---------------+--------------+------+-------------------------------+
|  1 |             4 |            1 |    1 | gwene.com.androidcentral      |
|  2 |             5 |            1 |    1 | gwene.com.androidcentral      |
|  3 |             6 |            1 |    1 | gwene.com.androidcentral      |
|  4 |             7 |            1 |    1 | gwene.com.androidcentral      |
|  5 |             8 |            1 |    1 | gwene.com.androidcentral      |
|  6 |             9 |            1 |    1 | gwene.com.androidcentral      |
|  7 |            10 |            1 |    1 | gwene.com.androidcentral      |
|  8 |            11 |            1 |    1 | gwene.com.androidcentral      |
|  9 |             4 |            2 |    2 | gwene.com.blogspot.emacsworld |
| 10 |             4 |            3 |    3 | gwene.com.blogspot.googlecode |
| 11 |             5 |            3 |    3 | gwene.com.blogspot.googlecode |
| 12 |             6 |            3 |    3 | gwene.com.blogspot.googlecode |
| 13 |             7 |            3 |    3 | gwene.com.blogspot.googlecode |
| 14 |             8 |            3 |    3 | gwene.com.blogspot.googlecode |
| 15 |             9 |            3 |    3 | gwene.com.blogspot.googlecode |
| 16 |            10 |            3 |    3 | gwene.com.blogspot.googlecode |
| 17 |            11 |            3 |    3 | gwene.com.blogspot.googlecode |
| 18 |             4 |            4 |    4 | gwene.com.economist           |
| 19 |             5 |            4 |    4 | gwene.com.economist           |
| 20 |             6 |            4 |    4 | gwene.com.economist           |
| 21 |             7 |            4 |    4 | gwene.com.economist           |
| 22 |             8 |            4 |    4 | gwene.com.economist           |
| 23 |             9 |            4 |    4 | gwene.com.economist           |
| 24 |            10 |            4 |    4 | gwene.com.economist           |
| 25 |            11 |            4 |    4 | gwene.com.economist           |
+----+---------------+--------------+------+-------------------------------+
25 rows in set (0.00 sec)

mysql> 
mysql>

[A Visual Explanation of SQL Joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html). — eggyal, Aug 04 '12 at 08:57
This mean that values(NEWSGROUP.ID) = value(ARTICLE.NEWSGROUP_ID) — Jérôme Boé, Aug 04 '12 at 09:08
possible duplicate of [Difference between left join and right join in SQL Server](http://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server) — Antonio, Sep 10 '15 at 13:54

Justin Swanhart · Answer 1 · 2012-08-04T09:37:44.797

Not really, because RIGHT JOIN and LEFT JOIN are symmetric. That is:

A LEFT JOIN B = B RIGHT JOIN A

RIGHT JOIN is merely syntactic sugar. The optimizer can rewrite a right join to a left join:

mysql> explain extended select * from t right join t t2 using (c1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: index
possible_keys: NULL
          key: c2
      key_len: 5
          ref: NULL
         rows: 4201
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.t2.c1
         rows: 1
     filtered: 100.00
        Extra: 
2 rows in set, 1 warning (0.00 sec)

Note the LEFT JOIN in the optimizer re-write (the tables are swapped):

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t2`.`c1` AS `c1`,`test`.`t2`.`c2` AS `c2`,`test`.`t`.`c2` AS
`c2` from `test`.`t` `t2` left join `test`.`t` on((`test`.`t`.`c1` = `test`.`t2`.`c1`))  where 1
1 row in set (0.00 sec)

Notice that (A RIGHT JOIN B != A LEFT JOIN B) unless (A INNER JOIN B = A LEFT JOIN B). This is because A RIGHT JOIN B is not symmetrical with A LEFT JOIN B (it is symmetrical with B LEFT JOIN A).

In your case, A RIGHT JOIN B will be the same as A LEFT JOIN B unless there are NULL values in the columns you are joining. If there are NULL values, then A LEFT JOIN B will NOT be the same as A RIGHT JOIN B. If you add new articles without adding the associated newsgroup (or vice-versa) then the results would change too.

score 3 · Accepted Answer · answered Apr 20 '15 at 07:06

3

Codeproject has this image which explains the simple basics of SQL joins, taken from: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx SQL joins explained enter image description here

answered Apr 20 '15 at 07:06

Mauro Midolo

1,841
3
14
34

score 1 · Answer 3 · answered Aug 04 '12 at 09:01

With your current data, yes they are the same. However as NEWSGROUP_ID is nullable then they could be different as the data changes.

Personally I always use LEFT JOINs if possible (from primary table to child table), in fact I have only needed to use a RIGHT JOIN a couple of times in over 6 years of SQL development!

right join versus left join

3 Answers3

Linked

Related