How correctly to get multiple columns from subquery in PostgreSQL?

Question

Let's say I have such SQL query which I make to PostgreSQL database. As you can see I'm repeating the same subquery just to get another column. I am convinced that this is ineffective. What is the best way to execute an sql query for your opinion?

SELECT
    TABLE_A.COLUMN_1,
    TABLE_A.COLUMN_2,
    (
        SELECT
            TABLE_B.COLUMN_A,
        FROM 
            TABLE_B
        WHERE 
            TABLE_B.COLUMN_Z ILIKE CONCAT('%', TABLE_A.COLUMN_2, '%')
    ) AS COLUMN_3,
    (
        SELECT
            TABLE_B.COLUMN_B,
        FROM 
            TABLE_B
        WHERE 
            TABLE_B.COLUMN_Z ILIKE CONCAT('%', TABLE_A.COLUMN_2, '%')
    ) AS COLUMN_4,
FROM
    TABLE_A

marc_s · Accepted Answer · 2019-06-13 20:45:48Z

7

Use a lateral join:

SELECT a.COLUMN_1, a.COLUMN_2, b.*
FROM TABLE_A a LEFT JOIN LATERAL
     (SELECT b.COLUMN_A, b.COLUMN_B
      FROM TABLE_B b
      WHERE b.COLUMN_Z ILIKE CONCAT('%', a.COLUMN_2, '%')
    ) b
    ON 1=1;

edited Jun 13, 2019 at 20:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 12, 2019 at 10:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2019-06-13 20:44:39Z

1

Use join

SELECT
    TABLE_A.COLUMN_1,
    TABLE_A.COLUMN_2, TABLE_B.COLUMN_A AS COLUMN_3, ABLE_B.COLUMN_B AS COLUMN_4
FROM
    TABLE_A 
JOIN
    TABLE_B ON TABLE_B.COLUMN_Z LIKE CONCAT('%', TABLE_A.COLUMN_2, '%')

edited Jun 13, 2019 at 20:44

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 12, 2019 at 10:51

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

How correctly to get multiple columns from subquery in PostgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related