Question about Joins

Posted on 2005-04-24
Last Modified: 2012-05-05
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?
Question by:gdlp2004
    LVL 20

    Accepted 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.

    LVL 20

    Expert Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now