How to use sql join where table1 has rows not present in table2

I have two tables record and share. record has columns: name and id. share has columns id.

I want to find the rows which are present in record but not pre开发者_高级运维sent in share.

How can I do this?

---

SQL LEFT JOIN returns all rows from the left table even if there are no matches in the right table

SELECT name, id
FROM record r LEFT JOIN share s on r.id = s.id
WHERE s.id is null

---

You have this tables: RECORD (ID, NAME) SHARE(ID,VALUE)

If your SQL engine supports LEFT OUTER JOINS, the best way is:

SELECT RECORD.* FROM RECORD LEFT OUTER JOIN SHARE WHERE RECORD.ID=SHARE.ID
WHERE SHARE.ID IS NULL

Important
place an index on SHARE.ID

How it works:
SQL Engine span all the RECORD table looking for a record in SHARE, for each record in RECORD if it is found a "linked" record in SHARE the where clause is falso, so the record is not included in the result set, if no records are found in share the where clause is true and the RECORD.* is included in result set. This works thanks to LEFT OUTER JOIN.

Note:
Another way of doing the same thing is to use the WHERE RECORD.ID NOT IN (SELECT ID FROM SHARE). Pay attention that depending on the sql engine you are using this may lead to serious performance issues because the internal engine can run the (SELECT ID FROM SHARE) once per record in RECORD table.

---

select Id from t1 where id not in (select id from t2)

---

SELECT * from RECORD where ID not in (SELECT DISTINCT ID FROM SHARE);

SELECT DISTINCT ID FROM SHARE - will get all the distinct IDs in the table share

SELECT * from RECORD where ID not in (SELECT DISTINCT ID FROM SHARE); would display all records whose ID is not in the first query.