2

This is my SQL query:

SELECT
   last_name,
   department_name
FROM
   employees e,
   departments d
WHERE
   e.department_id = d.department_id;

And:

SELECT
   last_name,
   department_name
FROM
   employees e INNER JOIN
   departments d ON e.department_id = d.department_id;

What is difference between that?
And which is better and faster query in SQL Server ?

Behzad
  • 3,502
  • 4
  • 36
  • 63
  • 2
    This are implicit and explicit join notations, nothing to with T-SQL or PL/SQL. – potashin Jun 17 '15 at 19:52
  • @suslov no any different between that ? and equals ? – Behzad Jun 17 '15 at 19:54
  • 2
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style (your example #1) was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is **discouraged** – marc_s Jun 18 '15 at 05:06

2 Answers2

6

What is difference between that?

AFAIK, both are doing INNER JOIN, First one using a Implicit JOIN syntax whereas the second one using a explicit join syntax

I wouldn't expect any performance difference between them but the second style of query using explicit join syntax is much more recommended over the first one cause it's ease of readability and clears the idea as what you are trying to perform. Also, the first style of writing is a old style join syntax.

Rahul
  • 76,197
  • 13
  • 71
  • 125
3

Performance-wise or result-wise there shouldn't be a difference. With SQL server, the query optimizer actually converts your query to the best found execution plan. Both of you queries would end up as the same execution plan.

If using SQL server, run them both with the "display execution plan" option turned on.

That will tell you both how they're executed (if there's any difference at all) and the relative performance difference.

As others have said, one is preferred, but on syntax not performance grounds.

Dylan Cross
  • 544
  • 2
  • 6
  • 14
  • 1
    Not a bad suggestion, but it doesn't actually answer the question. That's what comments are for. – Air Jun 17 '15 at 22:43