SQL JOIN Basics
How to we define Joins?
- You can retrieve and manipulate data from more than one table in a single SELECT statement with using JOIN.
- You are going to use columns from multiple tables to pair tables.
- You can join columns with different names as long as the columns you are connecting are join-compatible.
- It is best if join columns have exactly the same data type.
Why Are Joins Necessary?
- It can happen that one table might not give you all the information you need about a particular entity.
- The relational model lets you partition your data using normalization and relies on the join operation to produce comprehensible reports.
- Joins are possible because of the relational model, and they are necessary because of the relational model.
How do we associating Data from Separate Tables?
- You can discover new relationships among data in different tables by joining them.
Generally there are two ways to connect two or more tables together.
Using the word JOIN between the tables:SQL server, MySQL, Oracle, PostgreSQL :
select First_name "First name", Last_name "Last Name", email, Telephone_type "Phone type", Telephone_number as "Phone number" from customers c JOIN telephones t on (c.customer_id= t.customer_id);Using comma between the tables:SQL server, MySQL, Oracle, PostgreSQL :
select First_name "First name", Last_name "Last Name", email, Telephone_type "Phone type", Telephone_number as "Phone number" from customers c, telephones t where c.customer_id= t.customer_id;The result should be for both examples:
First name Last Name Phone type Phone number Ricard Nixon Ricard.Nixon@hotmail.com Work (978) 667-94 Ricard Nixon Ricard.Nixon@hotmail.com Home (978) 667-94 Robert Redford R.Redford@google.com Unknown (678) 223-94 Robert Redford R.Redford@google.com Home (679) 234-94 Judith Bowman J.Bowman@imb.com Cell (371) 153-54 Judith Bowman J.Bowman@imb.com Work (371) 143-54 Sandra Emerson Sandra.Emerson@mySql.com Unknown (881) 141-54 Sandra Emerson Sandra.Emerson@mySql.com Unknown Marcy Darnovsky Marcy@oracle.com Unknown (881) 141-54 Marcy Darnovsky Marcy@oracle.com Unknown
Join Provides Flexibility
- If a table must be split into two tables, you can always reconstruct the rows using the join.
- If new and related information appears, you can design tables with appropriate join columns, so the new data can be linked to the existing data.
How to get a Good Join?
- Ideally, a join column is a key column for its table - either as a primary key or as a foreign key.
- When a key is composite, you can join on all the columns of the key.
- Key columns are usually ideal candidate columns on which to construct a join.
Rules for a join is to have meaningful results:
- Tables must be named in the table list in order for the system to perform the join.
- The columns in the join must have related meaning.
- Columns that participate in a JOIN does not need to have the same name, although they often will.
- Beware of that columns with NULLs will never join because NULLs represent unknown or inapplicable values.
Performance is often an issue with joins. Here are some performance guidelines:
- Join columns should be indexed.
- Join columns should have exactly the same datatypes.
© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the
correctness in the pages or source codes.
The risk of using this web-site pages or any program
codes from this website is entirely at the individual user.