CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Steve Graber
Steve Graber

Reputation: 297

SQL syntax for LEFT OUTER JOIN in SQL Server 2012

We're having an issue with SQL Server 2012 due to the lack of support for the *= (LEFT OUTER JOIN) operator.

Can anyone tell me what is the correct syntax for SQL Server 2012 for the following SQL that worked correctly on SQL Server 2008?

SELECT 
    t7410.name, t7408.type, t7410.length, 
    t7410.status, t7410.prec, t7410.scale, 
    t7409.type 
FROM 
    dbo.syscolumns t7410, dbo.systypes t7408, 
    dbo.sysobjects t7409, dbo.sysusers t7411, 
    master.dbo.syslogins t7412 
WHERE 
    t7410.id = t7409.id 
    AND t7411.uid = t7409.uid 
    AND t7409.name = 'GENERAL'
    AND t7409.type IN ('U', 'S', 'V') 
    AND t7410.usertype *= t7408.usertype 
    AND t7412.sid = t7411.sid 
    AND t7412.name = user_name() 
ORDER BY 
    t7410.colid ASC

Upvotes: 7

Views: 18409

Answers (1)

Taryn
Taryn

Reputation: 247680

Why not write this using ANSI JOIN syntax:

SELECT t7410.name, t7408.type, t7410.length, 
    t7410.status, t7410.prec, t7410.scale, 
    t7409.type 
FROM dbo.syscolumns t7410
INNER JOIN dbo.sysobjects t7409
    ON t7410.id = t7409.id
INNER JOIN dbo.sysusers t7411
    ON t7411.uid = t7409.uid 
INNER JOIN master.dbo.syslogins t7412 
    ON t7412.sid = t7411.sid 
LEFT JOIN dbo.systypes t7408
    ON t7410.usertype = t7408.usertype 
WHERE t7409.name = 'GENERAL'
    AND t7409.type IN ('U', 'S', 'V') 
    AND t7412.name = user_name() 
ORDER BY t7410.colid ASC

Upvotes: 19

Related Questions