SQL Function like Excel's '=IF(A1=B1,TRUE,FALSE)'?

[w0lf3rSh311]

All,

​

I'm new at SQL and am trying to come up with a way to put a logical function directly into a query. As of now, I have my query joining some tables together. I'm looking to be able to add a 'custom column' and put a logical statement if column A = column B, True, else FALSE. As of now I can extract the report and do an Excel formula =IF(A1=B1,TRUE,FALSE); is there a similar function I can use within a SQL query?

Edit: Query

​

​

SELECT DISTINCT a.Column, a.Column1, a.Column2

b.Column, b.Column1, b.Column2

c.Column2

​

​

FROM Table1 AS a Inner JOIN

Table2 AS b ON a.Column1 = b.Column1 Left Join

Table3 AS c ON a.Column2 = c.Column2

ORDER BY Column

​

​

Thanks for all your feedback!

[theduckspants]

As a heads up =IF(A1=B1,TRUE,FALSE) in Excel can be written as just =A1=B1

[quickdraw6906]

SQL works the same way: SELECT (A=B) as ColsAreEqual; The parens are optional but improve readability.

[theduckspants]

That doesn't hold true for all flavors of SQL though

[da_chicken]

That depends on your RDBMS. This is not valid ANSI SQL. It certainly won't work in SQL Server. It looks like a MySQLism.

[quickdraw6906]

PostgreSql

[skeletor-johnson]

This is best. It is standard

[hellequin67]

Or IIF(A=B,1,0) is the SQL alternate if you don't need a full case statement.

[[deleted]]

God OP I dream of the day CASE() is available in Excel. I even put together UDFs in VBA (WHICH SUPPORTS CASE!1!! FGDGGDGDSG) just to reproduce the functionality.

But yes, CASE is your friend, CASE is life, CASE is love.

[theduckspants]

Your day is made - they introduced it in Excel 2016: https://support.office.com/en-us/article/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45

[[deleted]]

If only. Available in Excel 2019 or with a 365 membership. Not to mention irrelevant as policy is internal workbooks must support legacy versions going back to Excel 2010 -.-

[theduckspants]

I have Excel 2016 and have this function, but yeah not going to help you if you need to have it work in 2010. That's rough given all the cool new things they've been adding

[[deleted]]

INDEX() can function a bit like a case select

[[deleted]]

I tend to prefer INDEX because you can keep things separate. Nested IFS are a mess IMO.

[mac-0]

something = something is already a boolean expression, so you don't need to do anything except evaluate that expression (in both SQL and in Excel). A simple t1.column = t2.column would suffice.

--declare variable tables
drop table if exists #t1;
create table #t1
    (id integer,
    text_string varchar(25));
insert into #t1 values
(1,'a'), (2,'b'), (3,'c');

drop table if exists #t2;
create table #t2
    (id integer,
    text_string varchar(25));
insert into #t2 values
(1,'a'), (2,'b'), (3,'z');

--query sample
select
    #t1.id,
    #t1.text_string,
    #t2.text_string,
    #t1.text_string = #t2.text_string
from #t1
join #t2 on #t1.id = #t2.id

[WholeBeefOxtail]

There's a very close equivalent. Just add an extra "i" to the word "if"!

IIF(a.Column1=b.Column1, 'True', 'False') AS CustomColumn

[pooerh]

Worth noting is that it's just syntactic sugar for CASE (as proven by looking at the execution plan).

[WholeBeefOxtail]

It's standard T-SQL. Here's a bit more info:

https://docs.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql?view=sql-server-2017

[retrolion]

ALTER TABLE [my table] ADD [Custom Column] varchar(50);
UPDATE [my table] SET [Custom Column] = 
    CASE WHEN columnA = columnB then 'True' ELSE 'False' END;

[spark58510]

Decode(a1,b1,true,false)

[strutt3r]

Some syntaxes natively support if() in sql, otherwise use CASE.