DATA with BARAA

SQL FULL JOIN

In this tutorial, you will learn how to fetch data from two tables using the FULL JOIN clause. 

Once you know how to fetch your data using  SELECT and FROM and the next step is to learn how to filter your data using WHERE clause.

In the previous tutorial, you learned how to retrieve all your rows from tables, but in real-world scenarios, we usually select only the rows which fulfill certain conditions like customers who come from a certain country.

All rows from both Tables

Syntax

The basic syntax of the WHERE clause to filter the data returned by a query can be given with:

				
					SELECT column_names
FROM table_name1 
FULL JOIN table_name2 
ON column_name1 = column_name2
				
			
				
					SELECT column_names
FROM table_name1 
LEFT JOIN table_name2 
ON column_name1 = column_name2

UNION ALL

SELECT column_names
FROM table_name1 
RIGHT JOIN table_name2 
ON column_name1 = column_name2
				
			

It’s real easy to read in a plain-English way: select some columns from a table, except that the results will only include rows where that fulfill my conditions.

  • The clauses need to be in this order e SELECT  FROM WHERE
  • The WHERE clause  appears immediately after the FROM clause
  • In WHERE clause, you can specify one or more comparisons and logical operators.

Examples

To understand the ORDER BY statement in a better way, let’s look at the following customers and orders tables in our tutorial database:

Customers
customer_id first_name last_name country score
1 Maria Cramer Germany 350
2 John Steel USA 900
3 Georg Pipps UK 750
4 Martin Müller Germany 500
5 Peter Franken USA NULL
Orders
order_id customer_id order_date quantity
1001 1 2021-01-21 250
1002 2 2021-04-05 1150
1003 3 2021-06-18 500
1004 6 2021-08-31 750
Customers
customer_id first_name last_name country score
1 Maria Cramer Germany 350
2 John Steel USA 900
3 Georg Pipps UK 750
4 Martin Müller Germany 500
5 Peter Franken USA NULL
Orders
order_id customer_id order_date quantity
1001 1 2021-01-21 250
1002 2 2021-04-05 1150
1003 3 2021-06-18 500
1004 6 2021-08-31 750
.

Now, let’s check out some examples that demonstrate how it actually works.

We have the following task be to solve using SQL statements

List customer ID, first name, order ID, quantity. Include all orders, Include All customers and all orders

The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.

You can have the same the result set by skipping the ASC, because it is the default option in ORDER BY.

				
					SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    o.quantity
FROM customers AS c
FULL JOIN orders AS o
ON c.customer_id = o. customer_id
				
			
				
					SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    o.quantity
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o. customer_id

UNION

SELECT
    c.customer_id,
    c.first_name,
    o.order_id,
    o.quantity
FROM customers AS c
RIGHT JOIN orders AS o
ON c.customer_id = o. customer_id
				
			

After executing the above query, you’ll get the result set something like this:

As you can see the output contains everything the whole customers tables including all rows and columns.

Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.

Find all customers whose score is greater than 500
				
					SELECT
    *
FROM customers
WHERE score > 500
				
			

Once you specify multiple columns after ORDER BY, the Database will sort the result by the first column, then the new ordered list will be sorted again by next column.

Operators in WHERE Clause

You can filter your results in a number of ways using comparison and logical operators, which you’ll learn about in the next tutorials. I summarized in the following table the most important ones.

.
Share it !