MySQL-Joins

Jul 09, 2009 Author: Developer

As the name implies, joins combine data from multiple tables to create a single recordset. Many applications use extremely complex joins to return recordsets of data spanning across many different tables. Some of these joins use subqueries that contain even more joins nested within them. Since joins often comprise very complex queries, they are regarded as an advanced SQL concept andmany inexperienced developers try to avoid them—for better or worse, however, they are not quite as complicated as they are made out to be. There are two basic types of joins: inner joins and outer joins. In both cases, joins create a link between two tables based on a common set of columns (keys). An inner join returns rows from both tables only if keys from both tables can be found that satisfies the join conditions. For example:

SELECT *
FROM book INNER JOIN book_chapter
ON book.isbn = book_chapter.isbn;

As you can see, we declare an inner join that creates a link between book and book_chapter; rows are returned only if a common value for the isbn column can be found for both tables. Note that inner joins only work well with assertive conditions—negative conditions often return bizarre-looking results:

SELECT * FROM book INNER JOIN book_chapter ON book.isbn <> book_chapter.isbn;

You would probably expect this query to return a list of all the records in the book table that do not have a corresponding set of records in book_chapter—however, the database engine returns a data set that contains an entry for each record in book_chapter that does not match each record in book; the end result is, in fact, a dataset that contains every line in book_chapter repeated many times over (the actual size of the set depending on the number of rows between the two tables that do have matching values for their respective isbn columns).

tags: SQL Joins

views 3299
  1. Add New Comment