Difference between Where and Join on Id

Question

I recently saw this query, which finds all the party a client can go to:

SELECT *
FROM Party
    INNER JOIN Organizer on Organizer.OrganizerId = Party.OrganizerId
    LEFT JOIN Client on Client.ClientID = 1
    LEFT JOIN PartyRegistration on PartyRegistration.PartyId = Party.PartyId
WHERE Party.OrganizerId = 0
    AND (Party.HasGuestList = 0 OR PartyRegistration.ClientId = Client.ClientId)

I had never seen a join on a specific value before. Is it normal to see SQL code like this?

I don't have much knowledge of left joins but it can apply to any join, for example, how would this:

SELECT *
FROM Party
    INNER JOIN Organizer on Organizer.OrganizerId = 0

compare to that since the results are the same:

SELECT *
FROM Party
    INNER JOIN Organizer on Organizer.OrganizerId = Party.OrganizerId
WHERE Organizer.OrganizerId = 0

Answer 1

This is very good practice -- in fact, you cannot (easily) get this logic in a WHERE clause.

A LEFT JOIN returns all rows in the first table -- even when there are no matches in the second.

So, this returns all rows in the preceding tables -- and any rows from Client where ClientId = 1. If there is no match on that ClientId, then the columns will be NULL, but the rows are not filtered.