On 2015-06-11 01:36 PM, Igor Korot wrote:
> Clemens,
>
> On Thu, Jun 11, 2015 at 7:01 AM, Clemens Ladisch <clemens at ladisch.de>
> wrote:
>> Paul Sanderson wrote:
>>> I have just been asked whether implicit and explicit join are the same
>>>
>>> select text, handle.id from message,handle where handle_id = handle.ROWID;
>> This is an implicit *inner* join.
>>
>>> SELECT message."text", handle.id FROM message LEFT JOIN handle ON
>>> message.handle_id = handle."ROWID";
>> This is an explicit *outer* join.
>>
>> Outer joins are one reason for using explicit joins, but you want to
>> compare against a query with the same semantics:
>>
>> select text, handle.id from message join handle on handle_id =
>> handle.ROWID;
> But even outer joins can be rewritten to use standard semantics:
>
> SELECT <smth> FROM <table_names> WHERE <condition>;
>
> So what is the purpose to use joins at all?
How will this statement:
SELECT <smth> FROM <table_names> WHERE <condition>;
ever be able to also signify a left outer join?
I have never come across such an example, but if there is a way then I
would like to learn about it.
i.e. just to be sure we are on the same page, consider in this next SQL,
how can I write the second query (in the format above) to produce the
same results as the first Query?
CREATE TABLE T1(id INT, name TEXT);
CREATE TABLE T2(id INT, T1_Ref INT);
INSERT INTO T1 VALUES
(1, 'John'),
(2, 'James'),
(3, 'Joe');
INSERT INTO T2 VALUES
(1,1),
(2,4),
(3,3),
(4,7);
-- First Query
SELECT T2.id, T2.T1_Ref, T1.name
FROM T2
LEFT JOIN T1 ON T1.id=T2.T1_Ref
-- id | T1_Ref | name
-- ------------ | ------ | ------
-- 1 | 1 | John
-- 2 | 4 | Null
-- 3 | 3 | Joe
-- 4 | 7 | Null
-- Second Query
SELECT T2.id, T2.T1_Ref, T1.nameFROM T2, T1WHERE T1.id=T2.T1_Ref
-- id | T1_Ref | name
-- ------------ | ------------ | ------
-- 1 | 1 | John
-- 3 | 3 | Joe
