Tuesday, October 10, 2017

if else / case statements in MySQL queries

Being able to change what is displayed in the query or to display the results of a different column based on criteria in a WHERE clause is super helpful. Because this is so useful and something I use so frequently I writing a blog post on it.

Here is am example query I wrote. I want to display the Aurora "EndPoint" alias when I provide an IP Address. I've created two tables, one which has RDS/Aurora Cluster details, and one with RDS/Aurora Instance details. I've pulled this information from the AWS API and stored it locally to easily query it in a relational database. If the IP Address turns out to be the writer end point then I want the c.EndPoint column value to be displayed. If the IP Address is for reader end point then I want the c.ReaderEndpoint to be displayed. If there is only one instance in the cluster then this query will always return c.EndPoint (writer end point).


    SELECT
    IF(i.IsClusterWriter = 1,  c.EndPoint,  c.ReaderEndpoint ) AS alias
    FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx';

With a case statement, I could also write it like this:

    SELECT
    (case when (i.IsClusterWriter = 1)
 THEN
      c.EndPoint
 ELSE
      c.ReaderEndpoint
 END)
 as alias
     FROM RDSCluster c
    INNER JOIN RDSInstance i ON (i.DBClusterIdentifier = c.DBClusterIdentifier)
    WHERE i.IPAddress = 'xx.xx.xxx.xx'

Other examples:

https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query

No comments:

Post a Comment