SQL Query - opposite of join?

I have the following tables:

Table ROWS ( **RowId**, Title)

Table CELLS ( **CellId**, RowId, Title) 

Table ERRORS ( **ErrorId**, CellId, Title )

So rows can have cells and cells can have errors.

How do I write a query that retrieves the rows where the cells have no errors?

I know that something like this would work but I was wondering if it can be done in a more efficient manner :

QUERY1

SELECT ROWS.RowID FROM ROWS 
开发者_Python百科 JOIN CELLS ON ROWS.RowId = CELLS.RowID 
 JOIN ERRORS ON ERRORS.CellId = CELLS.CellId

QUERY2

SELECT * FROM ROWS 
WHERE ROWS.RowId NOT IN ( QUERY1 )** 

Any thoughts on how to do this in a more efficient manner ?

---

Try :
1)

SELECT R.RowID      
FROM ROWS  R LEFT JOIN 
(
    SELECT DISTINCT RowID
      FROM  CELLS  JOIN ERRORS       
              ON ERRORS.CellId = CELLS.CellId 
) C  ON R.RowId = C .RowID
 WHERE C.RowID  IS NULL 

2) OR

SELECT ROWS.RowID 
    FROM ROWS   JOIN CELLS 
   ON ROWS.RowId = CELLS.RowID   
WHERE NOT EXISTS
(
    SELECT 1
      FROM   ERRORS 
        WHERE ERRORS.CellId = CELLS.CellId 
)    

---

Some people have done research on this issue, see: http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

The conclusion is: use LEFT JOIN / IS NULL or NOT IN. Don't use NOT EXISTS; that is slower.

I think I would count the errors per row, and filter on that:

select cells.rowid from cells 
left join errors on errors.cellid=cells.cellid 
group by cells.rowid 
having count(errorid)=0;

This avoids all sub selects.

---

You can do this in a single query using the NOT EXISTS construct-- should be more efficient than what you have

select rows.rowid from rows where not exists (select 1 from cells join errors on errors.cellid = cells.cellid where cells.rowid = rows.rowid)

---

What about a LEFT JOIN and selecting all the rows that didn't find a partner?

SELECT ROWS.RowID
  FROM ROWS
  JOIN CELLS USING(RowID)
  LEFT JOIN ERRORS ON USING(CellID)
  WHERE ErrorID IS NULL

---

For sql server:

;WITH RowsWithErrors As 
(
    SELECT Distinct r.RowID
    FROM Rows r
    INNER JOIN Cells c ON c.RowID = r.RowID
    INNER JOIN Errors e on e.CellId = c.CellID
)
SELECT r.*
FROM Rows r
LEFT JOIN RowsWithErrors e ON r.RowID = e.RowID
WHERE e.RowID IS NULL

The LEFT JOIN/IS NULL technique is called an Exclusion Join, if you want to read more about it. So what this query does is first find rows that have errors, and then excludes them from the entire set of rows.