postgresql JOIN with multiple conditions

Question

I have two postgres tables:

worker_details_verification (verification_id BIGSERIAL, worker_id BIGINT, 
state TEXT, proofs TEXT[])
worker_details(worker_id BIGINT, name TEXT)

Now I want to get

    `verification_id, worker_id, proofs FROM` the table 
    `worker_details_verification`

restricting records `WHERE state = 'Initial'

Now in addition to the above three columns, I want the name column from the worker_details table too, where the worker_id can be used to query the worker's name.

I tried the following query, but it did not work.

SELECT a.verification_id, a.worker_id, a.state, a.proofs, b.Name FROM 
worker_details_verification a FULL OUTER JOIN worker_details b ON 
a.worker_id = b.worker_id AND a.state = 'Initial';

It returns records where even a.state is not 'Initial' and also some erroneous records where all name from worker_detail are returned with NULL for worker_details_verification columns.

Read up on the different types of joins: INNER< Left, right, full outer, cross join https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — xQbert, Sep 27 '17 at 16:10

score 6 · Accepted Answer · answered Sep 27 '17 at 16:10

6

It sounds to me that rather than a Full Outer Join, you'd want a Left/Right since you're looking for data from Worker_Details_Verification and then to filter that, while also grabbing Worker_Details where applicable.

I took this:

SELECT a.verification_id, a.worker_id, a.state, a.proofs, b.Name 
FROM worker_details_verification a 
     FULL OUTER JOIN worker_details b ON a.worker_id = b.worker_id AND a.state = 'Initial';

And made it into this:

SELECT a.verification_id, a.worker_id, a.state, a.proofs, b.Name 
FROM worker_details_verification a 
     LEFT OUTER JOIN worker_details b ON a.worker_id = b.worker_id 
WHERE a.state = 'Initial';

answered Sep 27 '17 at 16:10

Mike R

631
5
18

Both the answers work (INNER JOIN vs FULL OUTER JOIN). Are there any factors (such as performance) that can be used to choose one over the other ? – Sankar Sep 27 '17 at 16:16
I approach it based on how the data is related. If I know for certain that Worker_Details isn't missing any Worker_Ids that are in Worker_Details_Verification I would opt for an inner join. If there's uncertainty there I would use an outer join (left or right). I can only think of 1 particular time I've ever needed a Full Outer join in my career. – Mike R Sep 28 '17 at 17:51
oops. I meant: "LEFT OUTER JOIN (your answer)" and the INNER JOIN (the other answer). Not "Full outer join" (which I typed wrong). – Sankar Sep 29 '17 at 03:35
1

Little late to the party on this one, but the INNER join performance is going to be more performant than LEFT join in nearly all cases. There's a SO question that goes into more depth here : https://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server – Mike R Feb 28 '19 at 21:52

score 0 · Answer 2 · answered Sep 27 '17 at 16:05

0

Have you tried like this?

SELECT a.verification_id, a.worker_id, a.state, a.proofs, b.Name 
FROM worker_details_verification a 
INNER JOIN worker_details b ON a.worker_id = b.worker_id
WHERE a.state = 'Initial';

answered Sep 27 '17 at 16:05

A l w a y s S u n n y

36,497
8
60
103

Both the answers work (INNER JOIN vs FULL OUTER JOIN). Are there any factors (such as performance) that can be used to choose one over the other ? – Sankar Sep 27 '17 at 16:16

postgresql JOIN with multiple conditions

2 Answers2