SELECT R.RESERVATION_ID, R.TRIP_ID, R.TRIP_DATE
FROM RESERVATION AS R, TRIP AS T
WHERE T.STATE = 'ME'
AND R.TRIP_ID = T.TRIP_ID;I’m supposed to rewrite this using EXISTS, but I keep ending up with too many results. Any help? Thanks.
Been at least a decade since i touched any code.
Maybe this will help?
Yeah, I’m not sure what should be in the sub-query.
What flavor database and why are you changing it? Trying to get performance improvement or does the current query not return exactly the results you want?
It’s just MySQL homework. No performance gain, other than it’s due in less than two hours.
I updated the example to show which columns belong to which tables.
On no worries, I’m all for helping people cheat on homework.
As a join
SELECT R.RESERVATION_ID, R.TRIP_ID, R.TRIP_DATE
FROM RESERVATION AS R, TRIP AS T
WHERE T.STATE = ‘ME’
AND R.TRIP_ID = T.TRIP_ID;
As an EXISTS subquery
SELECT R.RESERVATION_ID, R.TRIP_ID, R.TRIP_DATE
FROM RESERVATION AS R
WHERE EXISTS
(
SELECT 1 FROM TRIP AS T WHERE T.STATE=‘ME’ AND R.TRIP_ID=T.TRIP_ID
);
Thanks! I actually got pretty close. My mistake was using both FROM tables in the outer query in your second example.
Isn’t using exists just worse? Maybe the sql engine makes them equivalent but I would never write it that way.
Yeah, that’s why I asked why he wanted EXISTS before I gave the equivalent subquery. But “homework” is one of the few valid reasons to make a perfectly good join harder to read.
I learned all this stuff before, but remember very little (apparently).
Most modern databases will reduce both forms to pretty much the same execution plan, so not necessarily. Regardless of performance, the first form is preferred because it’s more straightforward, but I could see the second one being used in certain kinds of software/modelling.
As a side note, I’d actually write that first query as
SELECT R.RESERVATION_ID, R.TRIP_ID, R.TRIP_DATE
FROM RESERVATION AS R
JOIN TRIP AS T
ON R.TRIP_ID = T.TRIP_ID
WHERE T.STATE = 'ME';
Because I like to make joins and join clauses very clear, and keep just “selectors” in the WHERE clause. Also, queries with INNER and OUTER joins will have the same structure in that way of writing, unlike the simpler one. The simpler form (without JOIN/ON keywords) would be:
SELECT R.RESERVATION_ID, R.TRIP_ID, R.TRIP_DATE
FROM RESERVATION AS R,
TRIP AS T
WHERE R.TRIP_ID = T.TRIP_ID
AND T.STATE = 'ME';
Quite similar, but join clauses come first in the WHERE clause.
Again, that’s the way I prefer. I’m not saying it’s the right way, but it has helped me a lot in my career. ;)
Don’t worry. It happens to most of us and that’s why an ORM framework/library is so convenient. I think CakePHP and Laravel are popular ones for PHP but you might need to get familiar with their frontend architecture pattern as well.
I can’t remember the last time I’ve used EXISTS because INNER/OUTER JOIN can get you the same results most of the time.
+1 to rhamorim preferred query semantics and I think that is how most ORM’s will form your query as well.
I am no RDBMS teacher but here is my take:
SQL queries usually form three parts:
- columns preceded by SELECT
- tables preceded by FROM
- filters preceded by WHERE (this is the interesting stuff most of the time)
I try to keep the filters as short as possible for readability. When queries start hitting 30+ lines with subqueries it’s easiest to focus on the filter of each query starting from the bottom up.
I sometimes still need a minute per line of undisturbed focus to even understand what someone tried to query once upon a time. So please do yourself a favor and use an ORM where possible unless you’re doing hardcore data science on years of data (where query optimization might be necessary). The pros are so plentiful including helping you filter input for SQL injection (most of them make prepared statements easy to define).
Great job on the keyboard mapping site for games and keep trucking! :)
Thanks! I’m not sure how much I can do with regard to ORM frameworks. I don’t own my own server, and my web host has old versions of PHP and MySQL installed, and disallows stored procedures.