Okay playing with this some more: it seems like since everything is equal to
the same constant, it doesn’t really matter? That said, it feels like poor form
/ not good practice (join can become not useful if you don’t have a constraint
joining particular columns). If I want to make it easier on myself to adjust
this query in the future, it’s better practice to move constants to the WHERE
clause and ensure there are column-based join constraints.
Does that seem right? My colleague presented me with a related query to debug,
and now I’m questioning everything.
Ben
> On Oct 27, 2019, at 3:45 PM, Benjamin Asher <benashe...@gmail.com> wrote:
>
> Oh you’re right. I realize now I messed up the example. Here are the updated
> queries:
>
> Query A
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE
> tab1.x='constant’;
>
> Query B
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant
>
> These return the same results in your sample setup. I think Simon pointed out
> what I’m also thinking is the problem: could Query A return rows that aren’t
> matched up properly because of a lack of a constraint joining the 2 tables?
>
> Thanks for putting together that more complete example :).
>
> Ben
>
>> On Oct 27, 2019, at 3:35 PM, Richard Hipp <d...@sqlite.org> wrote:
>>
>> On 10/27/19, Benjamin Asher <benashe...@gmail.com> wrote:
>>> It seems both of the following work,
>>> but I’m not really sure why:
>>
>> I get different answers for the two queries when I try them:
>>
>> CREATE TABLE tab1(x,y);
>> CREATE TABLE tab2(x,y);
>> INSERT INTO tab1 VALUES('constant','string'),('abc','def'),(97,15);
>> INSERT INTO tab2 SELECT * FROM tab1;
>>
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x='constant';
>>
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x='constant';
>>
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
