Discussion on: How to write SQL queries which are easier to read?

[Gpower2]

Hey javinpaul, nice article! I just wanted to add my 2 cents here, since I have also developed a style writing big and complex SQL queries.
In your example, I would write the query like this:

SELECT
    e.emp_id
    , e.emp_name
    , d.dpt_name
FROM
    Employee e
    INNER JOIN Department d
        ON e.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND e.emp_name LIKE '%A%'
    AND e.salary > 500

This style allows easily commenting out columns and criteria, identifying the tables and joins, along with the join criteria.

One more advanced example with CTE would be something like this:

;WITH E AS (
    SELECT 
        *
    FROM
        Employee
)
SELECT
    E.emp_id
    , E.emp_name
    , d.dpt_name
FROM
    E
    INNER JOIN Department d
        ON E.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND E.emp_name LIKE '%A%'
    AND E.salary > 500

Finally, you could write UNIONs easily with this style:

;WITH E AS (
    SELECT 
        *
    FROM
        Employee
)
SELECT
    E.emp_id
    , E.emp_name
    , d.dpt_name
FROM
    E
    INNER JOIN Department d
        ON E.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND E.emp_name LIKE '%A%'
    AND E.salary > 500

UNION

SELECT
    E.emp_id
    , E.emp_name
    , d.dpt_name
FROM
    E
    INNER JOIN Department d
        ON E.dept_id = d.dept_id
WHERE
    d.dept_name = 'finance'
    AND E.emp_name LIKE '%B%'
    AND E.salary > 500

[wara matsuluka]

@gpower2 I like your style and thats what we enforcing at our work place.

[javinpaul]

Hello @gpower2 , I must say your style is better than mine :-) thanks for sharing with us.

[Gpower2]

I'm really glad you liked it @javinpaul !
Always happy to see fellow devs trying to be better and share their thoughts with the community!