Developing By Me

Project Development and Design

FROM and JOIN

FROM

The FROM clause selects the tables will be included within the dataset and how they are joined to each other. SQL requires a minimum of one table in order to execute the query however through joining the query could use hundreds of tables joined in different ways which would add to the dataset. Joining tables together requires that the DBA knows the table structure and how the tables in the schema are interrelated. There are 4 different join statements which are JOIN, LEFT JOIN, RIGHT JOIN and OUTER JOIN. Each returns a unique result. The JOIN or INNER JOIN returns a row that contains a match in both of the tables. The LEFT JOIN will always return the previously listed table and if the new table has matching data that will be returned as well. The RIGHT JOIN is the reverse of the LEFT JOIN. This join returns all data from the new table even if the previous table has no matching data. The final join is the OUTER JOIN, FULL JOIN, or FULL OUTER JOIN which is rarely used because it returns all data from the tables even if there are no matches. The following is the standard syntax for joining tables:

 

SQL Syntax Description
FROM table1  Returns the rows from the specified table
FROM table1JOIN table2 ON table1.field1 = table2.field2  Returns rows that have data in both table1 and table2 based on the matching condition
FROM table1LEFT JOIN table2 ON table1.field1 = table2.field2  Always returns table1 and will return table2 if there is a match to the row from table1
FROM table1RIGHT JOIN table2 ON table1.field1 = table2.field2  Always returns table2 and will return table1 if there is a match to the row from table2
FROM table1OUTER JOIN table2 ON table1.field1 = table2.field2  Returns all data from both tables. If there is a match between the tables the rows will be combined

%d bloggers like this: