How can LEFT JOIN with UNION?-CodePudding

There are 2 same structure TABLE : Product01 and Product02. I use UNION to merge two TABLE and select the row with %LIKE%. However LIKE '%"A1234"%' change , always select same row.The following is sql code.

SELECT product_id
FROM Product01
UNION
SELECT product_id
FROM Product02
WHERE product_code LIKE '%"A1234"%'
ORDER BY product_id ASC

CodePudding user response：

You need to repeat the where for both selects:

SELECT product_id
FROM Product01
WHERE product_code LIKE '%"A1234"%'
UNION
SELECT product_id
FROM Product02
WHERE product_code LIKE '%"A1234"%'

You could also rewrite the query so the where applies to the result of the union:

WITH x AS (
SELECT product_id, product_code
FROM Product01
UNION
SELECT product_id, product_code
FROM Product02)
SELECT product_id 
from x 
WHERE product_code LIKE '%"A1234"%'
ORDER BY product_id ASC