query 1:

table_1 join table_2 using(col 1) where table_1.date_1 >= table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 = table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 < table_2.date_4);

What is the difference between the execution and expected results from the 2 queries?

Query 1 returned on 10% of records from query_1 while query 2 returned the desired result.

I had a doubt with respect to the ANSI syntax for join. How would the execution of the query change if i use the two queries given below –

query 1:
table_1 join table_2 using(col 1) where table_1.date_1 >= table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 = table_2.date_2 and table_1.date_1 >= table_2.date_3 and table_1.date_1 < table_2.date_4);

When i used query 1, my total records were reduced from 3 million to 600k. But it was not the case when i used the query 2.

I am unable to understand what went wrong in query 1.

I’m saying this syntax is not allowed in MySQL anyway: you HAVE to put parenthesis with USING.

FROM film JOIN film_actor USING film_id, x

as to which of the two parenthesized versions above it should be parsed.

FROM film JOIN film_actor USING (film_id, x)

(where x is a column)

vs

FROM (film JOIN film_actor USING film_id) , x

(where x is a table)

Great examples on multiple choices (multiple FKs, multiple columns names address_id).

With those examples it becomes very clear how being declarative SQL becomes ambiguous or undefined.

I agree to the notion of RELATE using a FK.

To be honest, I think SQL is a mess. It also uses ancient syntax rules. This doesn’t mean I don’t enjoy it, though 🙂

But it is my belief that ANSI SQL could have been much better if redesigned, in many aspects.