quote:

---

SELECT *
FROM product_traffic AS TableA INNER JOIN (SELECT * FROM product_price) AS TableB ON (TableA.pr_id = TableB.pr_id AND TableA.cl_id = TableB.cl_id) OR (TableA.pr_id = TableB.pr_id)

---

SELECT *
FROM product_traffic A
INNER JOIN product_price B ON A.pr_id = B.pr_id
WHERE A.cl_id = B.cl_id
  OR A.pr_id NOT IN (
    SELECT A.pr_id
    FROM product_traffic A
    INNER JOIN product_price B ON A.pr_id = B.pr_id AND A.cl_id = B.cl_id)

/* Just for the sake of sanity, let's assume the columns
   in question are composite pks */
CREATE TABLE product_traffic (
  pr_id int NOT NULL,
  cl_id int NOT NULL,
  CONSTRAINT pk_pt PRIMARY KEY CLUSTERED (pr_id, cl_id))


CREATE TABLE product_price (
  pr_id int NOT NULL,
  cl_id int NOT NULL,
  CONSTRAINT pk_pp PRIMARY KEY CLUSTERED (pr_id, cl_id))


/* Give the tables 15000 p(0.5) Bernoulli trials
   (that's posh for coin-tosses) each */
DECLARE @pr_max int, @cl_max int
SET @pr_max = 1000
SET @cl_max = 15


DECLARE @pr int, @cl int
SET NOCOUNT ON
SET @pr = 0
WHILE @pr < @pr_max
BEGIN
  SET @cl = 0
  WHILE @cl < @cl_max
  BEGIN
    IF RAND() < 0.5
      INSERT INTO product_traffic VALUES (@pr, @cl)
    IF RAND() < 0.5
      INSERT INTO product_price VALUES (@pr, @cl)
    SET @cl = @cl + 1
  END
  SET @pr = @pr + 1
END
SET NOCOUNT OFF


/* just check how many in each */
SELECT (SELECT COUNT(*) FROM product_traffic) AS pt_count,
  (SELECT COUNT(*) FROM product_price) AS pp_count


/* So one of these... */
SELECT * 
FROM product_traffic A
INNER JOIN product_price B ON A.pr_id = B.pr_id


SELECT * 
FROM product_traffic A
INNER JOIN product_price B
  ON A.pr_id = B.pr_id AND A.cl_id = B.cl_id


/* ...is supposed to be the same as either this... */
/* (ok, so I had to edit Robert's to get it to parse) */
SELECT A.pr_id AS A_pr, A.cl_id AS A_cl, B.pr_id AS B_pr, B.cl_id AS B_cl
INTO #temp
FROM product_traffic A
INNER JOIN product_price B ON A.pr_id = B.pr_id AND A.cl_id = B.cl_id


/* UNION ALL would be ok since they're disjoint by definition */
SELECT * 
FROM #temp
UNION
SELECT *
FROM product_traffic A
INNER JOIN product_price B ON A.pr_id = B.pr_id
WHERE A.pr_id NOT IN (SELECT A_pr FROM #temp)


DROP TABLE #temp


/* ...or this */
SELECT *
FROM product_traffic A
INNER JOIN product_price B ON A.pr_id = B.pr_id
WHERE A.cl_id = B.cl_id
  OR A.pr_id NOT IN (
    SELECT A.pr_id
    FROM product_traffic A
    INNER JOIN product_price B ON A.pr_id = B.pr_id AND A.cl_id = B.cl_id)