Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 558
  • Last Modified:

Question about Joins

This question is kind of a two parter, but since they are related I'll section them off for ease of readability.

1) I have never once used Inner Joins or Outer Joins, I learned mySQL by just looking at code, and trial and error.  I have always done my queries like so:

select * from question q, answer a where q.questionNumber="&questionNumber&" and q.quizId ="&quizNumber&" and q.questionVersion = "&questionVersion&" and q.questionid = a.questionid"

I take it the q.questionid = a.questionid part is a Join, right?  If so or if not, what is the difference between this and using Inner/Outer Joins?  

2) I use SqlYog now so it takes care of the relationship code for me...but I remember back in the day when I took my Database Admin. class in college, we had to create a relationship first, via sql code Create Relationship or something like that, without the benefit of a tool like SqlYog.  Will my above code sample work without creating a relationship, or is that what Joins are for, to create a query without creating a relationship?
  • 2
1 Solution
1.  Using the WHERE syntax above is an implicit INNER JOIN.  There are several types of JOIN statements..
   A) INNER JOIN  --  only the rows where the join conditions match are included in the query
   B) LEFT OUTER JOIN  --  similar to an inner join for the rows whose columns match between the tables.  However,all rows
       from the "left named" table are included in the query output whether they match or not.  Selected columns from the
       "right named" table will be null.
   C) RIGHT OUTER JOIN  --  just like B) execpt the roles of the LEFT/RIGHT named tables are reversed.
   D) FULL OUTER JOIN (aka FULL JOIN)  --  very similar to B) and C) except all rows from both tables appear in the query
   E) CROSS JOIN  --  every row in the left table is joined with every row in the right table.  Also known as a "cartesian
   F) SELF JOIN  -- technically, this isn't a diferent join type.  It refers to joins performed between a table and itself.

2.  Relationships between different tables are defined via the "FOREIGN KEY" clause(s).  Their purpose is to establish "referrential integrity"... this means you cannot have a value in a column that is defined in a table as "referring" to a column in a second table unless the value actually exists in the second table.  Nor can you delete a row (or changer a column) in the second table if any row in the first table(s) contain a value matching the FK's column value.  Yes, your WHERE clause above works just fine without the columns being defined as FK's.  However, the columns named in the WHERE clause (and in any JOIN clauses if you were using them) should be indexed to aid in query performance.  WIthout indexes, the query engine will have to scan the entire table to determine which rows match.  In most circumstances, this is far slower than using indexes to locate the same rows.

I should have mentioned in my first comment that inner joins are also known as "natural joins" and that using the WHERE clause isn't technically a JOIN... it just behaves like one.  Also, using that syntax may eventually be phased out by Microsoft in favor of the then-current ANSI standard leaving the WHERE clause only capable of specifying query conditions.  In order to promote better "cross-product" standardization it is recommended to follow the ANSI standards rather than proprietary syntax (unless you have strong reasons for doing so like better performance or extended functionality... just be aware of the risks you might be incurring if a future platform-change occurs).

-- Lynn

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now