Question 246546 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL- INNER JOIN use makes me confused

I’m learning SQL, and I’ve just learned about inner joins. The thing is, I’d been taught some other way to join tables before, and I don’t understand the difference

For example, the way I already knew:

SELECT E.SALARY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DP = D.DP AND D.CITY = 'LONDON';

This would show the salary of the employees that work on a department located in London

Now, the other way, using join:

SELECT E.SALARY
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D ON D.CITY = 'LONDON'
AND D.DP = E.DP;

EDIT: I’ve since edited the question, as I left out some of the code for the example. Sorry for the confusion, everyone

What’s the difference between the two? Wouldn’t both examples show the employees working in a department in London? Thanks

I’ve looked up in w3schools the JOIN clause, but still didn’t understand what would be the difference between those two ways

Tags:

2

Answers


  1. 0

    The first example (modified from your orginal statement) …

    1. SELECT E.SALARY FROM EMPLOYEES E, DEPARTMENTS D WHERE D.CITY='LONDON';

    … will produce the same results as …

    1. SELECT E.SALARY FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON D.CITY='LONDON';

    or even …

    1. SELECT E.SALARY FROM EMPLOYEES E INNER JOIN DEPARTMENTS D WHERE D.CITY='LONDON';

    But the first example, which uses an obsolete syntax, should not be used in modern code. You run the risk of one day it not being supported and then your code stops working. It is also less clear than the other two examples.

    By the way, the 2nd and 3rd SQL statements only produce the same results in general because an inner join is being performed. You will in general get different results from using the ON condition vs. the WHERE clause if you were doing an outer join.

    But I am not sure any of these statements will produce your desired results.

    You will be returned every row from EMPLOYEES multiple times, once for each row in DEPARTMENTS where the city is London. Perhaps you mean something like:

    SELECT E.SALARY FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON D.CITY='LONDON' AND D.DEPARTMENT_NUMBER = E.DEPARTMENT_NUMBER

    Login or Signup to reply.
  2. 0

    Your first example is this:

    SELECT E.SALARY 
  FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DP = D.DP
   AND D.CITY='LONDON';

    It can be rewritten, without changing its meaning, as an inner JOIN like this:

         SELECT E.SALARY 
       FROM EMPLOYEES E
 INNER JOIN DEPARTMENTS D ON E.DP = D.DP
      WHERE D.CITY='LONDON';

    Your first example uses your grandmother’s (really!) SQL comma-join syntax. In SQL-92 the explicit JOIN syntax was standardized.

    If you say this, without E.DP = D.DP, you get all possible pairs of rows in the two tables. That’s called a cross join. It’s very rarely what you want.

    SELECT E.SALARY 
  FROM EMPLOYEES E, DEPARTMENTS D 
 WHERE D.CITY='LONDON';

    This, an inner join without an ON-clause, gets you the same large and probably useless cross-join result.

         SELECT E.SALARY 
       FROM EMPLOYEES E
 INNER JOIN DEPARTMENTS D 
      WHERE D.CITY='LONDON';

    Inner joins remove all rows from the result set that don’t match the ON clause. You can use a LEFT JOIN if you want to keep the rows from the first table that have no match in the second table. This gets you all employees, including those that aren’t assigned to any department.

         SELECT E.SALARY 
       FROM EMPLOYEES E
  LEFT JOIN DEPARTMENTS D ON E.DP = D.DP
      WHERE D.CITY='LONDON';

    In the industry we like using the newer JOIN syntax because it makes LEFT JOIN operations much more straightforward to write. (RIGHT JOIN also exists, but we don’t use it much.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search