SQLPad
Instruction
- Write a query to return the total number of actors from actor_tv, actor_movie with
FULL OUTER JOIN. - Use
COALESCEto return the first non-null value from a list. - Actors who appear in both tv and movie share the same value of
actor_idin both actor_tv and actor_movie tables.
Table 1: actor_movie
Actors who appeared in a movie.
col_name | col_type ------------+------------------- actor_id | integer first_name | character varying last_name | character varying
Table 2: actor_tv
Actors who appeared in a TV show.
col_name | col_type ------------+------------------- actor_id | integer first_name | character varying last_name | character varying
Sample results
count ------- 123
Expected results
Solution postgres
SELECT COUNT(DISTINCT actor_id) FROM (
SELECT
COALESCE(T.actor_id, M.actor_id) AS actor_id
FROM actor_tv T
FULL OUTER JOIN
actor_movie M
ON M.actor_id = T.actor_id
) X;
Explanation
This query is counting the number of distinct actor IDs that appear in either the "actor_tv" table or the "actor_movie" table. It does this by first joining the two tables on the "actor_id" column, and using the COALESCE function to select the "actor_id" value from whichever table it appears in (if it appears in both, it will just select one of them). The resulting set of actor IDs is then counted using the COUNT and DISTINCT functions. The query is wrapped in a subquery with the alias "X".