Outer join equivalent

corss_join.sql

SELECT 
   distinct  p1.cid, p2.cid, p1.pid
FROM
    Purchase p1
        CROSS JOIN
    Purchase p2
WHERE
    p1.cid <> p2.cid AND p1.cid < p2.cid and p1.pid = p2.pid
	

outer_join_replacement.sql

SELECT 
    sid, sname, COUNT(bid)
FROM
    (SELECT 
        *
    FROM
        SAILORS
    JOIN RESERVES USING (sid)) AS TEMP
GROUP BY sid , sname 
UNION SELECT 
    s1.sid, s1.sname, COUNT(NULL)
FROM
    SAILORS s1
WHERE
    NOT EXISTS( SELECT 
            s1.sid
        FROM
            RESERVES r2
        WHERE
            s1.sid = r2.sid)
GROUP BY sid , sname;

Q1-e-p2

SELECT 
    p1.cid, p2.cid, p1.n + p2.n
FROM
    (SELECT 
        cid, COUNT(Temp.pid) AS n
    FROM
        (SELECT DISTINCT
        cid, pid
    FROM
        Purchase) AS Temp
    GROUP BY Temp.cid) AS p1
        CROSS JOIN
    (SELECT 
        cid, COUNT(Temp.pid) AS n
    FROM
        (SELECT DISTINCT
        cid, pid
    FROM
        Purchase) AS Temp
    GROUP BY Temp.cid) AS p2 ON p1.cid <> p2.cid AND p1.cid < p2.cid

References

http://stackoverflow.com/questions/15572333/left-outer-join-equivalent
http://stackoverflow.com/questions/18904778/sql-query-with-not-exists-not-working