Is there a tool to convert `where`s to equivalent `join`s?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Is there a tool to convert `where`s to equivalent `join`s?

Rocky Ji
In SQL world, generally
<https://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where>,
not just SQLite
<https://www.mail-archive.com/sqlite-users@.../msg114165.html>,
a lot <https://stackoverflow.com/questions/121631/inner-join-vs-where> of
pros say that the constructs `from...inner join...on` and `from...where`
are equivalent
<https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause>,
and that query optimizer should build identical plans for either variant.

Understanding `WHERE` is simple / straight-forward / intutive, to those
    1. without mathematics background, or
    2. used to imperative code and for-loops

So for learning sake, is there a tool that converts a query using `WHERE`
to a query (that yields identical results) using JOINs? Like a English ->
<AnyLanguage> side-by-side translator.

Cheers
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is there a tool to convert `where`s to equivalent `join`s?

Don V Nielsen
>   So for learning sake, is there a tool that converts a query using
`WHERE`
to a query (that yields identical results) using JOINs?

I'm not aware of a tool, but I would assert that not using a tool is for
learning sake. Recoding by hand is going to be your best learning
experience.

Note: there was a lot of good reading in the links you provided. One went
right down in logical order how Select statement is evaluated by SQL
engines. That there is the greatest tool for learning sake.


On Fri, Mar 1, 2019 at 2:15 AM Rocky Ji <[hidden email]> wrote:

> In SQL world, generally
> <https://stackoverflow.com/questions/5273942/mysql-inner-join-vs-where>,
> not just SQLite
> <
> https://www.mail-archive.com/sqlite-users@.../msg114165.html
> >,
> a lot <https://stackoverflow.com/questions/121631/inner-join-vs-where> of
> pros say that the constructs `from...inner join...on` and `from...where`
> are equivalent
> <https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause
> >,
> and that query optimizer should build identical plans for either variant.
>
> Understanding `WHERE` is simple / straight-forward / intutive, to those
>     1. without mathematics background, or
>     2. used to imperative code and for-loops
>
> So for learning sake, is there a tool that converts a query using `WHERE`
> to a query (that yields identical results) using JOINs? Like a English ->
> <AnyLanguage> side-by-side translator.
>
> Cheers
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is there a tool to convert `where`s to equivalent `join`s?

James K. Lowden
In reply to this post by Rocky Ji
On Fri, 1 Mar 2019 13:44:57 +0530
Rocky Ji <[hidden email]> wrote:

> So for learning sake, is there a tool that converts a query using
> `WHERE` to a query (that yields identical results) using JOINs? Like
> a English -> <AnyLanguage> side-by-side translator.

SQL-92 was introduced a long time ago.  At that time, I don't remember
any vendor offering any kind of automatic conversion tool.  Nor do I
know of one now.  

It's not that it couldn't be done.  It's that it wouldn't help.  

Given a database schema and a query, it's no problem to separate join
criteria from (relational) select criteria.  But equivalent
translations could be expressed several ways, and no machine-generated
conversion would necessarily communicate the intention of the query any
better.  Put another way: how likely is a machine to better pose a
query than the human being did in first place?  

If automatic translation adds functionality, that's different.  For
example, Oracle had a tool to convert T-SQL to PL/SQL.  While the
output was no one's concept of beauty, it did have the property of
running on Oracle.

The most important contribution of SQL-92 to the SELECT statement, as I
think Keith mentioned, wasn't separating JOIN from WHERE, but a clear,
standardized outer-join syntax.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users