Tuesday, August 23, 2011

Implicit vs explicit joins (SQL)

As I don't use MS-SQL Server (which many people take to be the standard, even if that is de facto and not de jure), I had not been aware of the debate regarding the syntax of implicit vs explicit joins until recently. At work, the ERP program has its own specific SQL syntax which is slightly different from the standard, especially with regard to joins, so this cannot serve as an example. It transpires that the version of Firebird which I use supports both implicit and explicit joins, so again, I wasn't aware of any difference.

What is explicit join syntax?
select meetings.curdate, meetings.price, people.forename, people.surname
from meetings inner join people
on meetings.person = people.id
where meetings.kind = 2
And what is implicit join syntax?
select meetings.curdate, meetings.price, people.forename, people.surname
from meetings, people
where meetings.person = people.id
and meetings.kind = 2
What is the difference?  The best explanation which I have found is (in my phrasing) the separation of the part which defines how the tables are joined from the part which defines how the rows are filtered. As Firebird is indifferent to which syntax is used (execution times were the same regardless of syntax), it doesn't matter too much which syntax I use. I will try, however, to start using explicit syntax as this is logically clearer.

Update
As this article points out, implicit syntax is otherwise known as SQL-89 join syntax, whereas explicit syntax is otherwise known as SQL-92 join syntax. I quote: "In this [SQL-89] syntax, join criteria are mixed with search criteria. It is applicable ONLY to inner joins. [In] SQL-89 there was no standard for outer joins."

So now you know. Or, seeing as everyone already knew this, now I know.

No comments: