Full Join in SQL

Using Full Joins

A FULL JOIN returns all the rows from the joined tables, whether they are matched or not i.e. you can say a full join combines the functions of a LEFT JOIN and a RIGHT JOIN. Full join is a type of outer join that’s why it is also referred as full outer join.

SQL Tutorial

SQL stands for Structured Query Language. SQL is a standard programming language specifically designed for storing, retrieving, managing or manipulating the data inside a relational database management system (RDBMS). SQL became an ISO standard in 1987.

SQL is the most widely-implemented database language and supported by the popular relational database systems, like MySQL, SQL Server, and Oracle. However, some features of the SQL standard are implemented differently in different database systems.

SQL was originally developed at IBM in the early 1970s. Initially it was called SEQUEL (Structured English Query Language) which was later changed to SQL (pronounced as S-Q-L).

What You Can Do with SQL

There are lot more things you can do with SQL:

• You can create a database.
• You can create tables in a database.
• You can query or request information from a database.
• You can insert records in a database.
• You can update or modify records in a database.
• You can delete records from the database.
• You can set permissions or access control within the database for data security.
• You can create views to avoid typing frequently used complex queries.

The list does not end here, you can perform many other database-related tasks with SQL. You will learn about all of them in detail in upcoming chapters.

What This Tutorial Covers

This SQL tutorial series covers all the fundamental concepts of SQL language, such as creating database and tables, using constraints, adding records to a table, selecting records from a table based on different conditions, updating and deleting records in a table, and so on.

Once you’re familiar with the basics, you’ll move on to next level that explains the methods of retrieving records through joining multiple tables, searching records in the table based on pattern, etc.

Finally, you’ll explore some advanced concepts, such as modifying the existing table structure, performing aggregations and grouping data together, creating views to simplify and secure table access, creating indexes for tables, handling dates and times, cloning of an existing table, creating temporary tables, as well as, concept of SQL subqueries and SQL injection.

What is Joins in SQL?

SQL Joins is used to combine rows of two or more tables by using common values.

Types of Joins in SQL

There are different types of Joins available in SQL:

• Inner Join in SQL
• Left Join in SQL
• Right Join in SQL
• Full Join in SQL

What is Inner Join in SQL?

Inner Join is a keyword, which is used to select all rows from the participating tables as long as there is a match between columns.

Inner Join in SQL

SQL Inner join basically gives records that have matching values in the participating tables. For example, let’s say, we have two tables, Table A and Table B. When inner join 

in SQL is applied on these two tables, it will get only those records which are common to both Table A and Table B

What is Left Join in SQL?

Left Join is a keyword, which is used to select all rows from the left table and also the matched values between the two tables.

Left Join in SQL

The Left Join in SQL basically returns all records from the left table and the matched records from the right tables. For example, let’s say, we have two tables, Table A and Table B. When Left Join is applied on these two tables, all records from Table A and only the matched records from Table B will be displayed.

What is Right Join in SQL?

Right Join is a keyword, which is used to select all rows from the right table and also the matched values between the two tables.

Setting Up Work Environment for Practicing SQL

If you have no DBMS (Database Management System) already installed on your system, you have several options to choose from. You can install a free, open-source DBMS.

MySQL is the most popular and widely supported open-source database management system. It is very easy to download and use and available for both Windows and Linux (or UNIX) operating system.

You can also install SQL Server Express. It is a free version of Microsoft SQL Server which allows up to 10GB of database storage.

Alternatively, if you’re planning to develop an application with PHP and MySQL you can install WampServer or XAMPP. WampServer is a Windows web development environment. It allows you to create web applications with Apache2, PHP and a MySQL database. It will also provide the MySQL administrative tool. PhpMyAdmin to easily manage your databases using a web browser.

This SQL Tutorial for Beginners is a complete package for how to learn SQL online. In this SQL tutorial , you will learn SQL programming to get a clear idea of what Structured Query Language is and how you deploy SQL to work with a relational database system.

So, a structured query language is a language that is used to operate the relational databases. Some of the major ways in which SQL is used in conjunction with a relational database is for the purposes of storing, retrieving and manipulating of data stored in a relational database.

What is SQL?

The language to communicate with the relational database is the SQL or Structured Query Language. SQL programming helps to operate the relational databases and derive information from it.

Some of the operations that the SQL does include creation of database, fetching, modifying, updating and deleting the rows along with storing, manipulating and retrieving data within the relational database. SQL programming is an ANSI standard language but there are a lot of versions of SQL in usage as well.

Why is SQL programming so widely used?

Structured Query Language or SQL programming is used so extensively for the following reasons.

• SQL lets you access any data within the relational database
• You can describe the data in the database using SQL
• Using SQL you can manipulate the data with the relational database
• SQL can embed within other languages through SQL modules & libraries
• SQL lets you easily create and drop databases and tables
• SQL allows you to create views, functions and stored procedures in databases
• Using SQL you can set permissions on procedures, tables and views.

Features of SQL

Here in this section of the SQL tutorial for beginners, we list some of the top features of SQL that make it so ubiquitous when it comes to managing relational databases.

• SQL is very simple and easy to learn language
• SQL is versatile as it works with database systems from Oracle, IBM, Microsoft, etc.
• SQL is an ANSI and ISO standard language for database creation and manipulation
• SQL has a well-defined structure as it uses long established standards
• SQL is very fast in retrieving large amounts of data very efficiently
• SQL lets you manage databases without knowing lot of coding.

Applications of SQL

Here in this section of the SQL tutorial we will learn SQL applications that make it so important in a data-driven world where managing huge databases is the norm of the day.

• SQL is used as a Data Definition Language (DDL) meaning you can independently create a database, define its structure, use it and then discard it when you are done with it
• SQL is also used as a Data Manipulation Language (DML) which means you can use it for maintaining an already existing database. SQL is a powerful language for entering data, modifying data and extracting data with regard to a database
• SQL is also deployed as a Data Control Language (DCL) which specifies how you can protect your database against corruption and misuse.
• SQL is extensively used as a Client/Server language to connect the front-end with the back-end thus supporting the client/server architecture
• SQL can also be used in the three-tier architecture of a client, an application server and a database which defines the Internet architecture.

Why should you learn SQL?

Today regardless of the relational databases systems by major corporations like Oracle, IBM, Microsoft and others, the one thing that is common to them is the Structured Query Language or SQL.

So if you learn SQL online then you will be able to pursue a very broad career spanning a lot of roles of responsibilities. Also if you are learning SQL then it is important for a data science career as well since a data scientist will also have to deal with relational databases and query it using the standard language SQL

What is SQL – Basic SQL Commands with example

What Is SQL?: SQL full form is Structured Query Language. SQL is a domain-specific language. It is used in application development language to enable a programmer to work with the data. The data is stored in a relational database. To manage this data, we have relational database management systems like SQL Server, MySQL, MS Access, etc. that use SQL as

How to install SQL Server?

Download and Install Microsoft SQL Server: Microsoft SQL Server is a relational database management system developed by Microsoft. The product’s primary function is to store and retrieve the data as requested by the user or another application. These SQL functions can either run on the same system or another over a network. Watch this video on How to install SQL

SQL Database Queries

How to Create, Use, and Drop a SQL Database ?: A database contains records, and to create, modify, or delete these records SQL queries are used. An SQL query can either be an action query like delete or create or a select query like select or use. These queries are used to find specific data, perform actions on it, or

Tables in SQL

Overview of Tables in SQL: Tables are the fundamental level to store data in a relational database management system. It contains a header row at the top of the table which gives the list of column names, followed by rows that contain data. we will discuss the basic operations performed on a database by using important SQL operators.

SQL Data Types

Data Types in SQL: SQL Data types inform a compiler or interpreter about the characteristics of the data used in a program. In simple words, a data type is a particular kind of data item which helps define the operations that can be performed on the data. There are different data types available like character data type for the letters

Create & Drop Table in SQL

How to Create and Drop Table in SQL: A database contains data, and to store data we need a table. Tables are the fundamental level of a database to store data. It comprises columns, which contain a set of data values of a particular type, and rows, which contains a single data item. 

DATE FORMAT in SQL

The DATE_FORMAT ()

It is a function from the SQL server. Date format in SQL is used for displaying the time and date in several layouts and representations.

SQL Date Format Functions

In Date Format in SQL, the format of the input date should be similar to the column’s format in the store. Matching both date and time’s format is important.

• NOW ()  – Returns the current date and time
• CURTIME() – Returns the current time
• EXTRACT() – Returns a single part of a date/time
• DATE_SUB() – Subtracts a specified time interval from a date
• DATE_FORMAT() – Displays date/time data in different formats
• DATEDIFF () – Returns the number of days between two dates
• DATE_ADD() – Adds a specified time interval to a date
• DATE() – Extracts the date part of a date or date/time expression
• CURDATE() – Returns the current date

SQL Date Functions

Some of the default date functions are:

• GETDATE () – Returns the time between two dates
• DATEADD () – Adds or subtracts a specified time interval from a date
• CONVERT () – Displays date/time data in different formats. It convert date format in SQL
• DATEPART () – Returns a single part of a date/time
• DATEDIFF () – Returns the current date and time

SQL Date Data Types

For storing date and time, the different data types are:

• DATE – in YYYY-MM-DD format in SQL
• YEAR – in YYYY or YY format in SQL
• TIMESTAMP – in YYYY-MM-DD HH: MI:SS format in SQL
• DATETIME – in YYYY-MM-DD HH: MI: SS format in SQL

What does a SQL FULL JOIN return?

• FULL JOIN returns all matching records from both tables whether the other table matches or not.
• FULL JOIN can potentially return very large datasets.
• FULL JOIN and FULL OUTER JOIN are the same.

The SQL FULL JOIN syntax

The general syntax is:

1. 1. SELECT column-names
2.  2. FROM table-name1 FULL JOIN table-name2
3. 3. ON column-name1 = column-name2
4. 4. WHERE condition

The general FULL OUTER JOIN syntax is:

1.  1. SELECT column-names
2.  2. FROM table-name1 FULL OUTER JOIN table-name2
3. 3. ON column-name1 = column-name2
4. 4. WHERE condition

---

SUPPLIER

Id

CompanyName

ContactName

City

Country

Phone

Fax

CUSTOMER

Id

FirstName

LastName

City

Country

Phone

SQL FULL JOIN Examples

Problem: Match all customers

and suppliers by country

1. 1. SELECT C.FirstName, C.LastName, C.Country AS CustomerCountry,
2. 2. S.Country AS SupplierCountry, S.CompanyName
3.  3. FROM Customer C FULL JOIN Supplier S
4. 4. ON C.Country = S.Country
5. 5. ORDER BY C.Country, S.Country

This returns suppliers that have no customers in their country,

and customers that have no suppliers in their country,

and customers and suppliers that are from the same country.

Result: 195 records

What Makes A Join?

Most subqueries can be rewritten as joins, and most joins can be rewritten as subqueries. A join defines two or more tables by a related column. Tables usually are joined on primary and foreign keys. For example, an employee table might have a primary key of an employee id column, while a timesheet table also has an employee id column that is a foreign key to the employee table. The SQL join can be written as “WHERE employee.empid = timesheet.empid” or “FROM employee JOIN timesheet ON (employee.empid = timesheet.empid).”

Advantages and Disadvantages of Joins

The main advantage of a join is that it executes faster. The performance increase might not be noticeable by the end user. However, because the columns are specifically named and indexed and optimized by the database engine, the retrieval time almost always will be faster than that of a subquery. There are also inner and outer joins, left and right joins, full joins and cross joins. A disadvantage of using joins is that they are not as easy to read as subqueries. Another disadvantage is that it can be confusing as to which join is the appropriate type of join to use to yield the correct desired result set.

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

SQL Full Join Syntax

The syntax of the Full Join in SQL Server is as shown below:

— SQL Server FULL JOIN Syntax

–OR We can Simply Write it as

For this SQL Server Full Join demonstration, We are going to use two tables (Employee and Department) present in our [SQL Server Tutorials] Database.

Data present in the Employee Table is:

Data present in the SQL Server Department Table is:

Full Join Select All Columns

The following Sql Server Full Outer Join Select all columns query will display all the columns and rows present in Employees and Department tables

— SQL Server FULL JOIN Example

OUTPUT

• If you observe the above screenshot, Although We have 15 records in the Employee table, SQL Full Outer Join is displaying 17 records. It is because there are two records in the Department table, i.e., Department Id 3, 4 (Module Lead and Team Lead), so 15 + 2 = 17 total records.
• For Department Id 3 and 4 (Module Lead and Team Lead), there are no matching records in the Employees table, so NULLS replaces them.
• ID number 10, 11, 14 and 15 of [DepartID], id, [Department Name], it is displaying NULL Values. It is because the Department Id for them in the Employee table is NULLS, so there are no matching records in the right table.

Full Join without Outer keyword

As we said before, it is optional to use an Outer keyword in this Join type. Let me remove the Outer keyword, and work will FULL JOIN

— SQL Server FULL JOIN Example

OUTPUT

NOTE: The [Department ID] column is repeated twice, which is annoying to the user. By selecting individual column names we can avoid unwanted columns so, Please avoid SELECT * Statements in Full Join

Full Join Select Few Columns

Please place the required columns after the SELECT Statement to avoid unwanted columns in full outer join

— SQL Server FULL JOIN Example

OUTPUT

Above SQL Full Join query will perfectly work as long as the column names from both tables (Employee and Department) are different like above.

What happens if we have the same Column names in both the tables? Well, you will end up in a mess. Let us see how to resolve the issue.

Before we get into the solution, let me show you one practical example. As you can see, we are using the above query. Still, we added id from the department table as an additional column.

— SQL Server FULL JOIN Example

As you can see from the below screenshot, it is throwing an error: Ambiguous column name id. It is because the id column is present in both Employee and department table. And SQL Server doesn’t know which column you are asking it to retrieve.

To resolve this kind of issue, you always have to use the table name before the column name.

The following Full outer Join query is using the ALIAS table name before the column names. By this approach, we can inform the SQL Server that we are looking for id column belonging to the department table.

We can write the above query as:

— SQL Server FULL JOIN Example

OUTPUT

SQL Full Join Where Clause

The Full Outer Join also allows us to use Where Clause to restrict the number of rows returned by the Full Join. In this example, we will use that WHERE Clause along with the Full Join.

— SQL Server FULL JOIN Example

OUTPUT

SQL Full Join Order By Clause

The Full outer Join allows us to use Order By Clause in Full Join to rearrange the order of the records.

— SQL Server FULL JOIN Example

OUTPUT