SQL Join Tables, SQL Tutorial, Global Guide Line Technology.
Bookmark Bookmark: SQL Join Tables, SQL Tutorial Forum | Blog |

      Home                    

Basic SQL Guide.           
SQL Introduction.               
 What is Table?                  
 SQL Create Table.            
 SQL Insert Statement.      
 SQL Select Keyword.        
 SQL Order by clause.        
 SQL Count Statement.      
 SQL Group By Clause.      
 SQL Having Clause.          
 SQL Alias Tables.             
 SQL Join Tables.
 SQL Outer Join.               
 SQL Update Statement.   
 SQL Delete Statement.    
 SQL Distinct Keyword.      
 SQL Where Keyword.       
 SQL And Or Keyword.      
 SQL In Keyword.              
 SQL Between Keyword.   
 SQL Like Keyword.          
 SQL Functions.                
 SQL Concatenate.           
 SQL Substrings.               
 SQL Trim Function.         
 SQL Constraints.              
 SQL Primary Key.            
 SQL Foreign Key.            
 SQL Create Views.           
 SQL Create Index.           
 SQL Alter Table.             
 SQL Drop Table.             
 SQL Truncate Table.      
 SQL Summary.                

SQL Tutorial >> SQL Join Tables.


     Back             Next     



Now in SQL Tutorial we want to look at SQL joins. To do joins correctly in SQL requires many of the elements we have introduced so far. Let's assume that we have the following two tables,

Table: Employees
 
FirstName LastName Email DOB Phone
Austin Hennery austin.it@gamail.com 24/11/1978 446 102-2222
Creston Narvon narvon_crest@hotmail.com 22/04/1976 443 325-4545
Kate Bladen kbladen@usa.net 01/05/1980 326 503-3232
Angela Julie julieee@yahoo.co.uk 11/12/1977 326 323-8888
Bobbi Ferguson bobi123@kiwibox.com 10/02/1970 506 252-9875
Cute Alfa cafa@gmail.com 19/05/1972 506 272-4566
 

Table Employees_Salary

FirstName Month Salary
Austin January 1000$
Creston January 1000$
Kate January 1000$
Angela January 1900$
Bobbi January 1000$
Cute January 1000$
Austin February 1100$
Creston February 1100$
Kate February 1100$
Angela February 1000$
Bobbi February 1200$
Cute February 1300$

Now we want to find out salary by employee name. We see that table Employees_Salary includes information about salary of all employees, and table Employees contains general information for each employee. To get the salary information for each employee by his or her name , we have to combine the information from these two tables. Examining the two tables, we find that they are linked via the common field, "FirstName". We will first present the SQL statement and explain the use of each segment later and we again use the alias for each table so that you will learn more about the alias.

Example of SQL Join Tables

SELECT emp.FirstName "First Name of Employee", SUM(empSal.Salary) Salary
FROM Employees emp, Employees_Salary empSal
WHERE emp.FirstName = empSal.FirstName
GROUP BY empSal.FirstName

Result of SQL Join Tables Example:

First Name of Employee Salary
Austin 2100$
Creston 2100$
Kate 2100$
Angela 2900$
Bobbi 2200$
Cute 2300$

On above example of SQL Tutorial express that the first two lines tell SQL to select two fields, the first one is the field "FirstName" from table Employees (aliased as First Name of Employee) and the second one is the sum of the field "Salary" from table Employees_Salary (aliased as Salary). Notice how the table aliases are used here: Employees is aliased as emp, and Employees_Salary is aliased as empSal. Without the aliasing, the first line would become

SELECT Employees.FirstName "First Name of Employee", SUM(Employees_Salary.Salary) Salary

which is much more cumbersome. In essence, table aliases make the entire SQL statement easier to understand, especially when multiple tables are included.

Next, we turn our attention to line three, the SQL WHERE statement. This is where the condition of the SQL join is specified. In this case, we want to make sure that the content in "FirstName" in table Employees matches that in table Employees_Salary, and the way to do it is to set them equal. This WHERE statement is essential in making sure you get the correct output. Without the correct WHERE statement, a SQL Cartesian Join will result. Cartesian SQL joins will result in the query returning every possible combination of the two or whatever the number of tables in the FROM statement tables. In this case, a Cartesian SQL join would result in a total of 4 x 4 = 16 rows being returned.


     Back             Next     
 

[ About ] [ Contact ] [ Home ]
[ Links ] [ Site Map ] [ Services ] [ Privacy ]

Copyright 2005 -  2018 www.globalguideline.com All rights reserved. (Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher) 227 visitors are online now