SQL join syntax

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL join syntax

Joseph L. Casale
Hey guys,
Need a pointer some join syntax.

I have a couple where TableA is one to many on TableB.
TableB structure is three columns Id,Key,Value. For each
row I select in Table, I join TableB several times for each
Key whose value I want in the single row result. How do
you construct the join and predicate so that for when a
given key in TableB is not present, the result is null? A left
join alone is not enough, the WHERE/AND excludes all data
when one of the joins is not satisfied.

SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableB
LEFT JOIN TableB B ON TableA.Id=B.Id
LEFT JOIN TableB C ON TableA.Id=C.Id
WHERE TableA.ColB=42
 AND B.Key='BAR'
 AND C.Key='BAZ'
GROUP BY Name

Works fine when BAR and BAZ values exist in TableB.Key.

Thanks guys,
jlc
_______________________________________________
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: SQL join syntax

Joseph L. Casale
> Works fine when BAR and BAZ values exist in TableB.Key.

Move the predicate on to the join it seems?


_______________________________________________
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: SQL join syntax

Keith Medcalf
In reply to this post by Joseph L. Casale
SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableB
LEFT JOIN TableB B ON TableA.Id=B.Id
LEFT JOIN TableB C ON TableA.Id=C.Id
WHERE TableA.ColB=42
 AND (B.Key='BAR' or B.Key is null)
 AND (C.Key='BAZ' or C.Key is null)
GROUP BY Name


--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Joseph L. Casale
> Sent: Thursday, 27 April, 2017 17:15
> To: '[hidden email]'
> Subject: [sqlite] SQL join syntax
>
> Hey guys,
> Need a pointer some join syntax.
>
> I have a couple where TableA is one to many on TableB.
> TableB structure is three columns Id,Key,Value. For each
> row I select in Table, I join TableB several times for each
> Key whose value I want in the single row result. How do
> you construct the join and predicate so that for when a
> given key in TableB is not present, the result is null? A left
> join alone is not enough, the WHERE/AND excludes all data
> when one of the joins is not satisfied.
>
> SELECT TableA.ColA Name, B.Value BV, C.Value CV
> FROM TableB
> LEFT JOIN TableB B ON TableA.Id=B.Id
> LEFT JOIN TableB C ON TableA.Id=C.Id
> WHERE TableA.ColB=42
>  AND B.Key='BAR'
>  AND C.Key='BAZ'
> GROUP BY Name
>
> Works fine when BAR and BAZ values exist in TableB.Key.
>
> Thanks guys,
> jlc
> _______________________________________________
> 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: SQL join syntax

Simon Slavin-3

On 28 Apr 2017, at 12:21am, Keith Medcalf <[hidden email]> wrote:

> SELECT TableA.ColA Name, B.Value BV, C.Value CV
> FROM TableB

I think Keith means "FROM TableA" on that second line.  The rest looks perfect.

Simon.
_______________________________________________
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: SQL join syntax

Joseph L. Casale
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Thursday, April 27, 2017 5:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] SQL join syntax
   
> On 28 Apr 2017, at 12:21am, Keith Medcalf <[hidden email]> wrote:
>
>> SELECT TableA.ColA Name, B.Value BV, C.Value CV
>> FROM TableB
>
> I think Keith means "FROM TableA" on that second line.  The rest looks perfect.

Hey guys,
So what are the semantics of the predicate on the join versus the where clause?
Just curious...

Thanks a lot Keith and Simon!
jlc
_______________________________________________
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: SQL join syntax

Simon Slavin-3

On 28 Apr 2017, at 12:31am, Joseph L. Casale <[hidden email]> wrote:

> So what are the semantics of the predicate on the join versus the where clause?
> Just curious...

SELECT <columns from both tables> FROM <name of primary table>
JOIN <name of secondary table> WHERE <which records in secondary table you want>

Simon.
_______________________________________________
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: SQL join syntax

Igor Tandetnik-2
In reply to this post by Joseph L. Casale
On 4/27/2017 7:15 PM, Joseph L. Casale wrote:

> Hey guys,
> Need a pointer some join syntax.
>
> I have a couple where TableA is one to many on TableB.
> TableB structure is three columns Id,Key,Value. For each
> row I select in Table, I join TableB several times for each
> Key whose value I want in the single row result. How do
> you construct the join and predicate so that for when a
> given key in TableB is not present, the result is null? A left
> join alone is not enough, the WHERE/AND excludes all data
> when one of the joins is not satisfied.
>
> SELECT TableA.ColA Name, B.Value BV, C.Value CV
> FROM TableB
> LEFT JOIN TableB B ON TableA.Id=B.Id
> LEFT JOIN TableB C ON TableA.Id=C.Id
> WHERE TableA.ColB=42
>  AND B.Key='BAR'
>  AND C.Key='BAZ'
> GROUP BY Name
>
> Works fine when BAR and BAZ values exist in TableB.Key.

SELECT TableA.ColA Name, B.Value BV, C.Value CV
FROM TableA
LEFT JOIN TableB B ON (TableA.Id=B.Id AND B.Key='BAR')
LEFT JOIN TableB C ON (TableA.Id=C.Id AND C.Key='BAZ')
WHERE TableA.ColB=42
GROUP BY Name

The condition in the ON clause is evaluated while the join is performed;
conceptually, for each pair of rows in the two tables.

The condition in the WHERE clause is evaluated *after* all joins are
performed, further filtering the resultset thus produced. In case of an
outer join, this means it has to be prepared to deal with rows that have
NULL in some columns. If one is not careful, it's easy to accidentally
filter those rows out, thus defeating the point of an outer join.

In other words, when the condition is false in the ON clause of a LEFT
JOIN, the result is the row from left table combined with NULLs for
right table. When the condition is false in WHERE clause, the whole row
is dropped.

For a "normal" inner join, a condition has the same effect either way,
whether it appears in ON or WHERE clause.
--
Igor Tandetnik

_______________________________________________
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: SQL join syntax

Keith Medcalf
In reply to this post by Joseph L. Casale
On Thursday, 27 April, 2017 17:31, Joseph L. Casale <[hidden email]> inquired:

> Hey guys,
> So what are the semantics of the predicate on the join versus the where
> clause?

> Just curious...

When the joins are only equijoins (inner joins) of any type:

SELECT <columns>
FROM <table1>
JOIN <table2> ON <condition2>
JOIN <table3> ON <condition3>
WHERE <condition1>

is syntactic sugar for:

SELECT <columns>
FROM <table1>, <table2>, <table3>
WHERE (<condition1>)
AND (<condition2>)
AND (<condition3>)

and the latter case is the one that is always processed.  The processing is as if there were a cross join of all the tables done and then all the conditions applied to select the returned rows from the selection.  In actual fact the query optimizer decides which order to process the table loops and were to apply the conditions to generate the result most efficiently.  In other words, for normal equijoins, the ON clause is just a where clause and there is no special significance attached.

You could also say:

SELECT <columns>
FROM <table1>
JOIN <table2> ON (<condition1>) AND (<condition2>) AND (<condition3>)
JOIN <table3>

or even think of it as (though this is an error)

SELECT <columns>
FROM <table1> ON <condition1>
JOIN <table2> ON <condition2>
JOIN <table3> ON <condition3>

and it would have the same effect.

However, if you use "CROSS JOIN" then you are prohibiting the query planner from re-ordering the nested-loop order of the two tables mentioned to the left and right of the CROSS JOIN keywords.  The processing of the ON/WHERE clauses is, however, identical to an normal inner join.  CROSS JOIN merely specifies a specific visitation order for the tables to its left and right (outer and inner respectively).

However, in the case of a LEFT or OUTER join, the ON conditions attached to the table on the rhs of the join is "glued" to the inner loop scanning that table, and any tables in mentioned in that ON clause must be visited before the OUTER JOIN can be processed and ONLY the conditions in that ON clause are used in the inner loop of the rhs table.  Otherwise all equijoin ON clauses become WHERE clause filters and the query optimizer is free to re-order and optimize how they are visited and how the filtering is applied.

So, the processing order is
(1) "glue" any ON conditions that belong to an OUTER JOINed rhs table to that tables' inner loop, push all other ON conditions down into the WHERE clause.
(2) optimize the visitation order of the tables and apply applicable WHERE conditions into the outer loops.  Only the specified conditions in the ON clause of an outer  join are used to select candidates from the inner loop of an outer join (other conditions from the WHERE clause cannot be applied)
(3) apply any leftover WHERE conditions
(4) apply any GROUP BY (which may be optimized up to step 2)
(5) apply any HAVING conditions


** All tables mentioned in the ON clauses glued to an OUTER join rhs table must be visited before that outer join can be visited.

See https://sqlite.org/queryplanner.html
And https://sqlite.org/optoverview.html
And https://sqlite.org/queryplanner-ng.html




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