ALL,

Before MS push on all those {INNER, OUTER} {LEFT, RIGHT} JOIN ON thing there was a simple and not confusing FROM ... WHERE.

What I'm looking for is a way to write a following:

Consider having 2 tables:

CREATE TABLE foo(foo_id INTEGER PRIMARY KEY, text1 char(20));
CREATE TABLE bar(bar_id INTEGER PRIMARY KEY, text2 char(25), FOREIGN KEY foo_id REFERENCES foo(foo_id));

What I am looking for is a query that will return me all rows where foo.foo_id == bar.foo_id and all records from foo that does not have corresponding rows in bar, that is written with a simple WHERE clause.

I am writing a program in C and so I think that when parsing the query text it will be easier to parse it then when using all those ugly joins.

SELECT text1, text2 FROM foo, bar WHERE ( /* condition goes here */ );

I already have a syntax for all other conditions, just this one is needed.

TIA!!

CodePudding user response：

So, you effectively want a left join between foo and bar which using modern syntax would be:

SELECT f.text1, b.text2
FROM foo f LEFT JOIN bar b
ON f.foo_id = b.foo_id

Using outdated non-proprietary syntax:

SELECT f.text1, b.text2 
FROM foo f, bar b
WHERE f.foo_id = b.foo_id
UNION ALL
SELECT text1, NULL
FROM foo
WHERE NOT EXISTS (SELECT 1 FROM bar WHERE foo.foo_id = bar.foo_id)

Or deprecated SQL Server:

SELECT f.text1, b.text2
FROM foo f, bar b
WHERE f.foo_id *= b.foo_id

CodePudding user response：

Not an answer, but an example of ambiguity

Old join syntax

Note that in the where clause we have ambiguity. One field defines an outer join, the other defines an inner join. This is a trivial example. In the real world with more complex queries, this ambuguity invites bugs. I was very resistant to SQL-92 But once this bit me (in Oracle) I forced myself to switch to SQL-92. That was probably over ten years ago. There's really no excuse to not be using SQL-92 syntax any more.

SELECT f.text1, b.text2
FROM foo f, bar b
WHERE f.foo_id *= b.foo_id
AND   f.z_id = b.z_id

SQL 92 syntax

There can be no ambiguity here as the join type is defined between tables.

SELECT f.text1, b.text2
FROM foo f
LEFT OUTER JOIN bar b
ON f.foo_id = b.foo_id
AND   f.z_id = b.z_id