How do I print out 'NULL' or '0' values for column values when an element isn't found?

I need to loop through a set of values (less than 10) and see if they are in a table. If so, I need to print out all of the record values, but if the item doesn't exist, I still want it to be included in the printed result, although with NULL or 0 values. So, for example, the following query returns:

select * 
  from ACTOR 
 where ID in (4, 5, 15);

+----+-----------------------------+-------------+----------+------+
| ID | NAME                        | DESCRIPTION | ORDER_ID | TYPE |
+----+-----------------------------+-------------+----------+------+
|  4 | [TEST-1]                    |             |        3 | NULL |
|  5 | [TEST-2]                    |             |        4 | NULL |
+----+-----------------------------+-------------+----------+------+

+----+-----------------------------+-------------+----------+------+
| ID | NAME                        | DESCRIPTION | ORDER_ID | TYPE |
+----+-----------------------------+-------------+----------+------+
|  4 | [TEST-1]                    |             |        3 | NULL |
|  5 | [TEST-2]                    |             |        4 | NULL |
|  15| NULL                        |             |        0 | NULL |
+----+-----------------------------+---开发者_如何学Python----------+----------+------+

Is this possible?

To get the output you want, you first have to construct a derived table containing the ACTOR.id values you desire. UNION ALL works for small data sets:

SELECT *
  FROM (SELECT 4 AS actor_id
          FROM DUAL
        UNION ALL
        SELECT 5
          FROM DUAL
        UNION ALL
        SELECT 15
          FROM DUAL) x

With that, you can OUTER JOIN to the actual table to get the results you want:

   SELECT x.actor_id,
          a.name,
          a.description,
          a.orderid,
          a.type
     FROM (SELECT 4 AS actor_id
             FROM DUAL
           UNION ALL
           SELECT 5
             FROM DUAL
           UNION ALL
           SELECT 15
             FROM DUAL) x
LEFT JOIN ACTOR a ON a.id = x.actor_id

If there's no match between x and a, the a columns will be null. So if you want orderid to be zero when there's no match for id 15:

   SELECT x.actor_id,
          a.name,
          a.description,
          COALESCE(a.orderid, 0) AS orderid,
          a.type
     FROM (SELECT 4 AS actor_id
             FROM DUAL
           UNION ALL
           SELECT 5
             FROM DUAL
           UNION ALL
           SELECT 15
             FROM DUAL) x
LEFT JOIN ACTOR a ON a.id = x.actor_id

Well, for that few values, you could do something ugly like this, I suppose:

SELECT 
   *
FROM
   (
      SELECT 4 AS id UNION 
      SELECT 5 UNION 
      SELECT 15
    ) ids 
      LEFT JOIN ACTOR ON ids.id = ACTOR.ID

(That should work in MySQL, I think; for Oracle you'd need to use DUAL, e.g. SELECT 4 as id FROM DUAL...)

That is only possible using a temporary table.

CREATE TABLE actor_temp (id INTEGER);
INSERT INTO actor_temp VALUES(4);
INSERT INTO actor_temp VALUES(5);
INSERT INTO actor_temp VALUES(15);
select actor_temp.id, ACTOR.* from ACTOR RIGHT JOIN actor_temp on ACTOR.id = actor_temp.id;
DROP TABLE actor_temp;

If you know the upper and lower limits on the ID, it's not too bad. Set up a view with all possible ids - the connect by trick is the simplest way - and do an outer join with your real table. Here, I've limited it to values from 1-1000.

select * from (
   select ids.id, a.name, a.description, nvl(a.order_id,0), a.type
   from Actor a,
        (SELECT level as id from dual CONNECT BY LEVEL <= 1000) ids
   where ids.id = a.id (+)
)
where id in (4,5,15);

Can you make a table that contains expected actor ids?

If so you can left join from it.

SELECT * FROM expected_actors LEFT JOIN actors USING (ID)