So please, avoid it.So my problem is the following. It also makes certain things - like LEFT JOINs - difficult to do, and increases the possibility of accidently sabotaging yourself. This becomes especially beneficial when dealing with ORs and ANDs in JOIN and WHERE conditions - many people lose track of what's going to be effected.Īlso note that the implicit-join syntax (the comma-separated FROM clause) is considered bad practice in general, or outright deprecated on some platforms (queries will still run, but the db may scold you). Regardless of how well you know operator-precedence rules, always put in parenthesis when mixing operations. Note that the parenthesis in Stars.starTemp - (50 * Planets.orbitDistance) is only there for clarity for the reader, the meaning of the math would remain unchanged if they were removed. Stars.starTemp - (50 * Planets.orbitDistance) as planetTempįROM (SELECT starId, name AS starName, (class + 7) * intensity * 1000000 AS starTemp We'll only have to list the calculation for starTemp once! SELECT Stars.starName, Stars.starTemp, Thankfully, we can move the Stars portion of this into a subquery.
Hopefully, you'll remember to change both references if that's necessary.
Well, that's slightly messy, but it works. (note that the db may actually be smart enough to perform the starTemp calculation only once per-line, but when writing you have to mention it twice in this context). ((Stars.class + 7) * Stars.intensity * 1000000) - (50 * Planets.orbitDistance) as planetTemp Which means we need to put in the calculation again: SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp, What's going on? The problem is that the new column alias (name) isn't (usually) available until after the SELECT part of the statement runs. except that on most RDBMSs, you'll get a syntax error stating the system can't find starTemp. Planets.name as planetName, starTemp - (50 * Planets.orbitDistance) as planetTemp Should be simple: SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp, Now we need to add the planet temperature. And we have all the stars back, with planetName being null (and only appearing once) if there are no planets for that star. But we still need to report stars without any planets! So what about a LEFT (OUTER) JOIN? SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp, Except now we have a problem: Some of our stars from the first query disappeared! The (INNER) JOIN is causing only stars with at least one planet to be reported. The ON clause is using an = (equals) condition to link planets to the star they orbit otherwise, we'd be saying they were orbiting more than one star, which is very unusual! Each star is listed once for every planet it has, but that's expected. What about an INNER JOIN (note that the actual keyword INNER is optional)? SELECT Stars.name as starName, (Stars.class + 7) * Stars.intensity * 1000000 AS starTemp, At this point, we don't need a disambiguating reference to source. We get a list of all stars whose starId is less than 100 (the WHERE clause), grabbing the name and calculating temperature. Here is the database schema (sorry, cant post the image file due to low rep): CREATE TABLE stars (starid INTEGER PRIMARY KEY,ĬREATE TABLE hilight (starid INTEGER UNIQUE) ĬREATE TABLE planets (planetid INTEGER PRIMARY KEY,ĬREATE TABLE moons (moonid INTEGER PRIMARY KEY,ĬREATE INDEX planets_starid ON planets (starid) ĬREATE INDEX moons_planetid ON moons (planetid) įirst, lets see about getting just the information about stars: SELECT name AS starName, (class + 7) * intensity * 1000000 AS starTemp LEFT OUTER JOIN (SELECT (startemp - 50 * planets.orbitdistance) AS planettemp LEFT OUTER JOIN (SELECT ((stars.class + 7) * stars.intensity) * 1000000 AS startemp Here is what I have: SELECT stars.name AS starname, startemp, planets.name AS planetname, planettemp What is the syntax to write a LEFT OUTER JOIN query when you have sub-query items "AS" that you need to join together?
Star with ((class+7)*intensity)*1000000, and a planet's temperature isĬalculated from the star's temperature minus 50 times orbit distance. Should have all stars, with the unknown data filled out with NULL. "starname", "startemp", "planetname", and "planettemp". Generate a list of stars with star ids below 100 with columns I can't figure out the following question (Exercise 12): I am learning SQL trough a GalaXQL tutorial.
0 kommentar(er)
