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 blog.codinghorror.com/a-visual-explanation-of-sql-joins — xQbert
– xQbert, Commented Sep 27, 2017 at 16:10

Mike R · Accepted Answer · 2017-09-27 16:10:55Z

11

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, 2017 at 16:10

Mike R

7011 gold badge5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sankar Over a year ago

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 ?

Mike R Over a year ago

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.

Sankar Over a year ago

oops. I meant: "LEFT OUTER JOIN (your answer)" and the INNER JOIN (the other answer). Not "Full outer join" (which I typed wrong).

Mike R Over a year ago

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 : stackoverflow.com/questions/2726657/…

A l w a y s S u n n y · Accepted Answer · 2017-09-27 16:05:46Z

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';

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 ?

Collectives™ on Stack Overflow

postgresql JOIN with multiple conditions

2 Answers 2

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Linked

Related