SQL Method of checking that INNER / LEFT join doesn't duplicate rows

Is there a good or standard SQL method of asserting that a join does not duplicate any rows (produces 0 or 1 copies of the source table row)? Assert as in causes the query to fail or otherwise indicate that there are duplicate rows.

A common problem in a lot of queries is when a table is expected to be 1:1 with another table, but there might exist 2 rows that match the join criteria. This can cause errors that are hard to track down, especially for people not necessarily entirely familiar with the tables.

It seems like there should be something simple and elegant - this would be very easy for the SQL engine to dete开发者_JAVA百科ct (have I already joined this source row to a row in the other table? ok, error out) but I can't seem to find anything on this. I'm aware that there are long / intrusive solutions to this problem, but for many ad hoc queries those just aren't very fun to work out.

EDIT / CLARIFICATION: I'm looking for a one-step query-level fix. Not a verification step on the results of that query.

---

If you are only testing for linked rows rather than requiring output, then you'd use EXISTS.

More correctly, you need a "semi-join" but this isn't supported by most RDBMS unless as EXISTS

SELECT a.*
FROM TableA a
WHERE EXISTS (SELECT * FROM TableB b WHERE a.id = b.id)

Also see:

• Using 'IN' with a sub-query in SQL Statements
• EXISTS vs JOIN and use of EXISTS clause

---

SELECT JoinField
FROM MyJoinTable
GROUP BY JoinField
HAVING COUNT(*) > 1
LIMIT 1

Is that simple enough? Don't have Postgres but I think it's valid syntax.

---

Something along the lines of

SELECT a.id, COUNT(b.id)
FROM TableA a
JOIN TableB b ON a.id = b.id
GROUP BY a.id
HAVING COUNT(b.id) > 1

Should return rows in TableA that have more than one associated row in TableB.