Sometimes you ponder which SQL syntax to use to combine data that spans over multiple tables. JOIN is a syntax often used to combine and consolidate one or more tables. Tables are joined two at a time making a new table which contains all possible combinations of rows from the original two tables.
The SQL NULL is the term used to represent a missing value. A NULL value in a table is a value in a field that appears to be blank. A field with a NULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. LEFT Joins joins the two table in such a way that it returns all the value from the left and matched value from right tables and also return null on right table when there is no match found. The structure for LEFT JOIN is: SELECT columnname(s) FROM table1. LEFT JOIN table2. ON table1.columnname=table2.columnname.
As a developer you may have different approaches towards handling database queries. MySQL Database For Beginners elaborates on how to join tables. Here are some of the more frequently used methods for consolidating queries on multiple tables into a single statement. While some of these options may affect performance, may increase or decrease the processing time, may pop up errors, but gradually through practice and experience you will know when to use which type of query.
If you need a refresher course on SQL coding and syntax, then SQL Database for Beginners and Learn SQL in 6 days are great courses to check out. They will guide you on how to perform operations on databases, how to perform Insert and Deletes in MS SQL, and how to insert data into tables. If you are new to writing SQL queries and want to learn how to build applications or generate business reports using SQL, then Introduction to SQL Training is a perfect match for you.
Methods to Join Multiple Tables
One simple way to query multiple tables is to use a simple SELECT statement. You can call more than one table by using the FROM clause to combine results from multiple tables.
Syntax:
SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;
The UNION statement is another way to return information from multiple tables with a single query. The UNION statement allows you to perform queries against several tables and return the results in a consolidated set, as in the following example:
Syntax:
SELECT column1, column2, column3 FROM table1
UNION SELECT column1, column2, column3 FROM table2;
This will return a result set with three columns containing data from both queries. By default, the UNION statement will omit duplicates between the tables unless the UNION ALL keyword is used. UNION is helpful when the returned columns from the different tables don’t have columns or data that can be compared and joined, or when it prevents running multiple queries and appending the results in your application code. As with subqueries, UNION statements can create a heavy load on your database server, but for occasional use they can save a lot of time.
SQL JOIN
With SELECT and UNION, some databases may have a limit on the number of tables that can be handled. That’s when it is a good idea to use the SQL JOIN statement to join two or more tables.
Types of SQL JOINS
Following are the different types of SQL joins:
- SQL INNER JOIN (or sometimes called simple join)
- SQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
- SQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
- SQL FULL OUTER JOIN (or sometimes called FULL JOIN)
- SQL CROSS JOIN
SQL INNER JOIN:
Definition: This returns all rows from multiple tables where the join condition is met or returns the records where table1 and table2 intersect. An INNER JOIN will only return rows for which there is data in both the tables. It specifies the complete cross product of two tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
SQL LEFT OUTER JOIN:
Definition: This type of join returns all rows from the LEFT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. This statement returns all records from table1 and only those records from table2 that intersect with table1.
Syntax
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
SQL RIGHT OUTER JOIN:
Description: This type of join returns all rows from the RIGHT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. In other words, this statement returns all records from table2 and only those records from table1 that intersect with table2.
Syntax
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
SQL FULL OUTER JOIN:
Definition: This type of join returns all rows from the LEFT-hand table and RIGHT-hand table. Where the join condition is not met, nulls are inserted. The result after the JOIN operation will be all the records from both table1 and table2.
Syntax
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.
![Join Two Table In Sql With Null Values Join Two Table In Sql With Null Values](https://d186loudes4jlv.cloudfront.net/sql/images/joins/cross-join.png)
SQL CROSS JOIN:
This will return all records where each row from the first table is combined with each row from the second table. A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.
Syntax:
SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]
OR
SELECT * FROM [TABLE 1], [TABLE 2]
Summary
Your success as a successful DBA lies in structuring and developing a properly designed and linked database, which will help you to retrieve information from as many tables as you want, specify retrieval conditions based on any data in the tables, and show the results in any order that you like. Also remember that an important factor about subqueries is performance. Each query is processed separately in full before being used as a resource for your primary query. So creative use of JOIN statements may provide better results with a lesser lag time.
Courses like SQL Server Essentials: What you should know! and Practical SQL Skills from Beginner to Intermediate will help you learn key SQL skills to boost your career.