Q:

Convert Subquery to Self Join

New to SQL and I understand joins tend to be faster than subqueries.  I have the following table, and my current query gives me the results I require, but I can’t wrap my head around a similar query that uses self join instead, assuming it’s possible.
Table
id           scheduled_id action_id
------------ ------------ ------------
1            1            1
2            1            2
3            1            3
4            2            1
5            2            2
6            3            1

Schema
create table ma (
id integer primary key,
scheduled_id integer,
action_id integer
);

insert into ma (
id,
scheduled_id,
action_id
)
values
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 1),
(5, 2, 2),
(6, 3, 1);

Query
select * from ma where action_id = 3
union all
select * from ma where scheduled_id not in (
  select scheduled_id from ma
  where action_id = 3)

Result
id           scheduled_id action_id
------------ ------------ ------------
3            1            3
4            2            1
5            2            2
6            3            1

My result should be all the rows that have the action_id value of 3 plus all the rows for those scheduled_ids that do not have an action_id value of 3.
The sqlfiddle may be found at http://sqlfiddle.com/#!5/0ba51/3.
Thank you.

A:

The result you are looking for using a self join is:
SELECT DISTINCT t1.*
FROM ma t1
JOIN ma t2  
ON  t1.SCHEDULED_ID <> t2.SCHEDULED_ID --Satisfies 2nd query
WHERE t2.ACTION_ID = 3 --Satisfies 2nd query
    OR  t1.ACTION_ID = 3 --Satisfies 1st query
ORDER BY t1.ID

