Geek asks and answers

Strange SQL join

Can someone explain to me which syntax matches this SQL query?

SELECT DISTINCT boats.boatid
FROM boats LEFT JOIN reservations ON
reservations.boatid = boats.boatid
and (@paramFromDate <= reservations.to AND reservations.from <= @paramToDate )
WHERE reservations.to IS NULL

tables:

**boats** boatid

**reservations** reservationid, fk_boatid, from, to

The idea behind the query is to get unreserved boats for a date range marked with parameters. Any boat that has any part of the range covered even partially is not available.

How is this "and ..." code there? Why is he missing something like the WHERE keyword? It seems WHERE is implicit here?

+3
source share
4 answers

JOIN, . , , , "this" AND "that" . WHERE, SQL , JOIN. , - LEFT JOIN. , , max minprice.

, .

SELECT DISTINCT beverages.beverageid
FROM beverages
LEFT JOIN invoices ON invoices.beverageid = beverages.beverageid AND (100 <= maxprice AND minprice <= 500)
WHERE maxprice IS NULL

( , )

+5

"and..." .

+1

. , WHERE.

, , >= 100, <= 500, ( null) maxprice minprice. , WHERE, , .

+1

LEFT JOIN, SQL Server, -, , maxprice 100, minprice - 500 NULL -, , .

In this case, as you do SELECT DISTINCT beverage, the LEFT JOIN will most likely be optimized as surface.

The final one WHEREis going to remove most of the rows from your query.

0
source

More articles:


All Articles