Default implicit parameters SQL

Question

I can't seem to figure out what's the point of having default implicit parameters in SQL (please see the example below), given that SQL is a declarative language. Isn't forcing the parameters a way of assuring more readability? After all, code is meant to be written once and read by humans many times (or at least once).

For example, in PostgreSQL we have the following syntax:

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression

The following query:

SELECT * 
FROM T1 JOIN T2
ON true;

implies an inner join meaning it is equal to the following query:

SELECT *
FROM T1 INNER JOIN T2
ON true;

can someone please explain to me the reason behind the idea of default and optional parameters (in our example the implicit type of join) in SQL?

Laurenz Albe · Accepted Answer · 2018-11-08 11:02:43Z

1

This syntax was devised by the SQL standard committee in its wisdom.

Nobody can accuse SQL of being excessively terse, but I guess there were some cases where the option to abbreviate the syntax was deemed appropriate (another case that comes to mind is CREATE TEMP[ORARY] TABLE).

You touched the question of readability, and I think it is a valid one. We can probably agree that very terse syntax hampers readability, but so can excessively verbose syntax.

I think that it was mostly a question of taste and judgement on the side of the people who wrote the standard.

answered Nov 8, 2018 at 11:02

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2018-11-08 12:03:47Z

Your question is a little hard to follow. INNER and OUTER are not parameters. They are keywords. The syntax diagram doesn't make this explicit, but SQL supports the following JOIN operators:

JOIN
INNER JOIN
LEFT JOIN
LEFT OUTER JOIN
RIGHT JOIN
RIGHT OUTER JOIN
FULL JOIN
FULL OUTER JOIN
CROSS JOIN

These are in pairs, except for the CROSS JOIN. In other words, the INNER and OUTER are optional.

Why is this the case? There is little "wisdom" in my opinion in setting the SQL standards. The standards committee consists (primarily) of industry representatives, from companies that often have already implemented the functionality. The standards then bring it together "compatibly". So, some members wanted explicit INNER and OUTER. Others did not. The "compromise" is to allow both.

As for your question those queries are equivalent. But the right way to express the logic eschews the ON clause:

SELECT * 
FROM T1 CROSS JOIN T2;

Collectives™ on Stack Overflow

Default implicit parameters SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related