7

I came across writing the query in differnt ways like shown below Type-I

SELECT JS.JobseekerID , JS.FirstName , JS.LastName , JS.Currency , JS.AccountRegDate , JS.LastUpdated , JS.NoticePeriod , JS.Availability , C.CountryName , S.SalaryAmount , DD.DisciplineName , DT.DegreeLevel FROM Jobseekers JS INNER JOIN Countries C ON JS.CountryID = C.CountryID INNER JOIN SalaryBracket S ON JS.MinSalaryID = S.SalaryID INNER JOIN DegreeDisciplines DD ON JS.DegreeDisciplineID = DD.DisciplineID INNER JOIN DegreeType DT ON JS.DegreeTypeID = DT.DegreeTypeID WHERE JS.ShowCV = 'Yes'

Type-II

SELECT JS.JobseekerID , JS.FirstName , JS.LastName , JS.Currency , JS.AccountRegDate , JS.LastUpdated , JS.NoticePeriod , JS.Availability , C.CountryName , S.SalaryAmount , DD.DisciplineName , DT.DegreeLevel FROM Jobseekers JS, Countries C, SalaryBracket S, DegreeDisciplines DD , DegreeType DT WHERE JS.CountryID = C.CountryID AND JS.MinSalaryID = S.SalaryID AND JS.DegreeDisciplineID = DD.DisciplineID AND JS.DegreeTypeID = DT.DegreeTypeID AND JS.ShowCV = 'Yes'

I am using Mysql database

Both works really well, But I am wondering

  1. which is best practice to use all time for any situation?
  2. Performance wise which is better one?(Say the database as a millions records)
  3. Any advantages of one over the other?
  4. Is there any tool where I can check which is better query?

Thanks in advance

Improve this question
3
  • edit: Scorpi0 mentions it quite well, be sure to take a look at his SO-links Commented Jun 29, 2010 at 8:05
  • nop, the DBMS detect the inner join, it doesn't perform a cross join Commented Jun 29, 2010 at 8:10
  • hmm, I think i read it somewhere in the MySQL forums, but you might be right about it. Maybe I can find the articlem thanks for pointing out though! Commented Jun 29, 2010 at 8:14

5 Answers 5

Reset to default
10

1- It's a no brainer, use the Type I

2- The type II join are also called 'implicit join', whereas the type I are called 'explicit join'. With modern DBMS, you will not have any performance problem with normal query. But I think with some big complex multi join query, the DBMS could have issue with the implicit join. Using explicit join only could improve your explain plan, so faster result !

3- So performance could be an issue, but most important maybe, the readability is improve for further maintenance. Explicit join explain exactly what you want to join on what field, whereas implicit join doesn't show if you make a join or a filter. The Where clause is for filter, not for join !

And a big big point for explicit join : outer join are really annoying with implicit join. It is so hard to read when you want multiple join with outer join that explicit join are THE solution.

4- Execution plan are what you need (See the doc)

Some duplicates :

Explicit vs implicit SQL joins

SQL join: where clause vs. on clause

INNER JOIN ON vs WHERE clause

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

in the most code i've seen, those querys are done like your Type-II - but i think Type-I is better because of readability (and more logic - a join is a join, so you should write it as a join (althoug the second one is just another writing style for inner joins)).

in performance, there shouldn't be a difference (if there is one, i think the Type-I would be a bit faster).

Improve this answer

Comments

1

Look at "Explain"-syntax http://dev.mysql.com/doc/refman/5.1/en/explain.html

Improve this answer

Comments

1

My suggestion.

Update all your tables with some amount of records. Access the MySQL console and run SQL both command one by one. You can see the time execution time in the console.

Improve this answer

Comments

1

For the two queries you mentioned (each with only inner joins) any modern database's query optimizer should produce exactly the same query plan, and thus the same performance.

For MySQL, if you prefix the query with EXPLAIN, it will spit out information about the query plan (instead of running the query). If the information from both queries is the same, them the query plan is the same, and the performance will be identical. From the MySQL Reference Manual:

EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the output in the order that MySQL would read them while processing the query. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

When the EXTENDED keyword is used, EXPLAIN produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. This information displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.

As to which syntax is better? That's up to you, but once you move beyond inner joins to outer joins, you'll need to use the newer syntax, since there's no standard for describing outer joins using the older implicit join syntax.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.