none
How to achieve left outer join? RRS feed

  • Question

  • How to achieve left outer join without using LEFT and *= key words? Is it possible to do that just with set based operation?
    • Edited by SSAS_user Sunday, January 2, 2011 6:44 AM
    Sunday, January 2, 2011 6:42 AM

Answers

  • I'd write this as

    create table t1
    
    (id int,
    
    name nvarchar(100)
    
    )
    
    insert into t1
    
    select * from
    
    (
    
    select 1 as id, 'a' as name
    
    union all
    
    select 2 as id, 'b' as name
    
    union all
    
    select 3 as id, 'c' as name
    
    ) t1;
    
     
    
     
    
    create table t2
    
    (id int,
    
    amount int
    
    )
    
    insert into t2
    
    select * from
    
    (
    
    select 1 as id, 1 as name
    
    union all
    
    select 1 as id, 2 as name
    
    union all
    
    select 2 as id, 3 as name
    
    union all
    
    select 2 as id, 4 as name
    
    union all
    
    select 4 as id, 5 as name
    
    ) t1
    
     
    
    select * from
    
    t1 inner join T2 on t1.id = t2.id
    
    union all
    
    
    
    select t1.*, null,null from t1
    
    where not exists (
    
    select 1 from
    
    T2 where t2.id = t1.id)
    
    
    
    
    I don't think there is a better version available except for the LEFT JOIN. I think LEFT JOIN and this version should be equiavalent.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by SSAS_user Sunday, January 2, 2011 7:16 AM
    • Unmarked as answer by SSAS_user Sunday, January 2, 2011 7:20 AM
    • Marked as answer by SSAS_user Sunday, January 2, 2011 11:52 AM
    Sunday, January 2, 2011 7:12 AM
    Moderator

All replies

  • You can do it with the INNER JOIN and a UNION for the rest of the records.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, January 2, 2011 6:53 AM
    Moderator
  • Thanks, yes, i tried it.

    create table t1

    (id int,

    name nvarchar(100)

    )

    insert into t1

    select * from

    (

    select 1 as id, 'a' as name

    union all

    select 2 as id, 'b' as name

    union all

    select 3 as id, 'c' as name

    ) t1;

     

     

    create table t2

    (id int,

    amonut int

    )

    insert into t2

    select * from

    (

    select 1 as id, 1 as name

    union all

    select 1 as id, 2 as name

    union all

    select 2 as id, 3 as name

    union all

    select 2 as id, 4 as name

    union all

    select 4 as id, 5 as name

    ) t1

     

    select * from

    t1  join T2 on t1.id = t2.id

    union

    (

    select t1.*, null,null from t1

    where t1.id not in(

    select t1.id from

    t1 join T2 on t1.id = t2.id)

    )

     

    But as you can see:

    1)    there is a subselect but I don’t like use it.

    2)    I have to apply nulls for all columns of t2. Is it possible to simple use one null for all columns?

     

    How to improve that?

     

    Sunday, January 2, 2011 7:04 AM
  • I'd write this as

    create table t1
    
    (id int,
    
    name nvarchar(100)
    
    )
    
    insert into t1
    
    select * from
    
    (
    
    select 1 as id, 'a' as name
    
    union all
    
    select 2 as id, 'b' as name
    
    union all
    
    select 3 as id, 'c' as name
    
    ) t1;
    
     
    
     
    
    create table t2
    
    (id int,
    
    amount int
    
    )
    
    insert into t2
    
    select * from
    
    (
    
    select 1 as id, 1 as name
    
    union all
    
    select 1 as id, 2 as name
    
    union all
    
    select 2 as id, 3 as name
    
    union all
    
    select 2 as id, 4 as name
    
    union all
    
    select 4 as id, 5 as name
    
    ) t1
    
     
    
    select * from
    
    t1 inner join T2 on t1.id = t2.id
    
    union all
    
    
    
    select t1.*, null,null from t1
    
    where not exists (
    
    select 1 from
    
    T2 where t2.id = t1.id)
    
    
    
    
    I don't think there is a better version available except for the LEFT JOIN. I think LEFT JOIN and this version should be equiavalent.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by SSAS_user Sunday, January 2, 2011 7:16 AM
    • Unmarked as answer by SSAS_user Sunday, January 2, 2011 7:20 AM
    • Marked as answer by SSAS_user Sunday, January 2, 2011 11:52 AM
    Sunday, January 2, 2011 7:12 AM
    Moderator
  • > How to achieve left outer join without using LEFT and *= key words? Is it possible to do that just with set based operation?

    Any particular reason you want this? In later post, you also said no to subselects and NULL.

    It's like asking if you can drive a car without a steering wheel and a brake.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Sunday, January 2, 2011 10:58 AM
  • > How to achieve left outer join without using LEFT and *= key words? Is it possible to do that just with set based operation?

    Any particular reason you want this? In later post, you also said no to subselects and NULL.

    I don't like to specify NULLs one by one. I konw the sets for union must have the same column, which means i have to specify nulls for each column. Is it possible to spcify NULLs for t2.* one time?

    No particular reason, I just want to better farmilar with T-SQL.

    Sunday, January 2, 2011 11:43 AM
  • For example, there is another version:

    WITH TEMP1 AS
    (
     SELECT *
     FROM dbo.t1
     UNION
     SELECT NULL, NULL
    ),
    TEMP2 AS
    (
     SELECT *
     FROM dbo.t2
     UNION
     SELECT NULL, NULL
    ),
    TEMP AS
    (
    SELECT ID
    FROM dbo.t1
    EXCEPT
    SELECT ID
    FROM dbo.t2
    )
    SELECT TEMP1.*,TEMP2.*
    FROM TEMP1,
    TEMP2,
    TEMP
    WHERE (TEMP1.id=TEMP2.id) OR ((TEMP2.ID IS NULL) AND (TEMP1.id=TEMP.id))
    
    

     

    Sunday, January 2, 2011 11:49 AM
  • I don't like to specify NULLs one by one. I konw the sets for union must have the same column, which means i have to specify nulls for each column. Is it possible to spcify NULLs for t2.* one time?

    No particular reason, I just want to better farmilar with T-SQL.

    In that case, learn to use LEFT JOIN, learn to use subqueries, and, yes, you will have to list multiple NULLs some times. But don't use *=. That syntax is very obsolete.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Sunday, January 2, 2011 11:53 AM