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	email	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:

  1. Tables must be named in the table list in order for the system to perform the join.
  2. The columns in the join must have related meaning.
  3. Columns that participate in a JOIN does not need to have the same name, although they often will.
  4. 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:

  1. Join columns should be indexed.
  2. Join columns should have exactly the same datatypes.