Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query outer join fixed values

I would like to query some IDs (fixed values) into a table, like a "outer join" with the mandatory fields should be the fixed values. I have to do the same for SQLServer and MySQL, but with one solution I could adapt it to the another. Example:

Table X:
ID  Name
1   A
2   B
3   C
4   D

Fixed Values:
1
3
10

In this case, I would like to get some like:

1   A
3   C
10  null

My query should be something like:

Select ID, Name
From X
Where X.ID IN (1,3,10)(+)
Tags:

2

Answers


  1. 0

    You could use a derived table and then a UNION ALL to SELECT your arbitrary values instead a serviced table:

    SELECT DT.ID,
       YT.Name
FROM(SELECT 1 AS ID
     UNION ALL
     SELECT 3 AS ID
     UNION ALL
     SELECT 10 AS ID) DT
    LEFT JOIN YourTable YT ON DT.ID = YT.ID;

    Honestly, however, use the tools you have at your disposal for each RDBMS. For SQL Server, a VALUES table construct is much simpler:

    SELECT V.ID,
       YT.Name
FROM (VALUES(1),(3),(10)) V(ID)
     LEFT JOIN YourTable YT ON V.ID = YT.ID;

    I’m not familiar with MySQL to comment if there’s a more succinct version than the UNION ALL method.

    Login or Signup to reply.
  2. 0 
    -- Using a CTE
WITH FixedValues AS (
    SELECT 1 AS ID
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 10
)
SELECT fv.ID, x.Name
FROM FixedValues fv
LEFT JOIN X x ON fv.ID = x.ID;

    Alternatively, you can use a subquery directly in the FROM clause:

    SELECT fv.ID, x.Name
FROM (
    SELECT 1 AS ID
    UNION ALL
    SELECT 3
    UNION ALL
    SELECT 10
) AS fv
LEFT JOIN X x ON fv.ID = x.ID;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search