Hey Friends 😁
It’s another day to learn something new.
Have you ever wondered what happens to all the sales you see inputted on computers at big malls or supermarkets? How do they manage and collect all that data? That’s where SQL comes in, especially SQL JOINS, which helps combine all the sales data made at different times into a single report.
Or think about a time when you got debited but didn’t receive the cash you withdrew from a POS shop in your street, and when you go to the bank, where do you think they retrieve your information from? The information comes from the backend, where relational database systems like SQL Server, Postgres, and MySQL come in handy.
But how do they join all those records into a single table? How do they remove duplicates, or sometimes, keep duplicates when combining data into one table?
That’s where SQL JOINS (like LEFT JOIN, RIGHT JOIN, INNER JOIN, and FULL JOIN) and SQL Operations like UNION and UNION ALL come into play.
SQL JOINS
SQL JOINS are used to combine data from two or more tables.
There are different types of JOINS that determine how the data from these tables are combined.
Here are some common types of SQL JOINS:
LEFT JOIN: This returns all the data from the left table and the matching data from the right table. If there’s no matching value in the right table, it returns NULL (which means no data).
RIGHT JOIN: This does the opposite. It returns all the data from the right table and the matching data from the left. If there’s no match in the left table, it returns NULL.
INNER JOIN: This only returns the rows where there is a match in both tables. If there’s no match, the row won’t be included.
FULL JOIN: This is a combination of LEFT JOIN and RIGHT JOIN. It returns all the data from both tables, and if there’s no match, it fills the missing data with NULL.
Example: How do you which is a Left Table and a Right Table?
Let’s say you have two tables in a shop in Lekki. One table lists all the employees, and the other table lists all the sales transactions they’ve made.
When you LEFT JOIN the employees table with the sales table, the left table is the employees table (because it’s the first one you’re selecting), and the right table is the sales table (the second one).
SELECT employees.employee_id, employees.name, sales.total_sales
FROM employees
LEFT JOIN sales
ON employees.employee_id = sales.employee_id;
In this example, we’re getting all the employees and their sales. If an employee hasn’t made any sales, the result will show NULL for their total sales, but the employee data will still show up.
So, the left table is the one you want all the data from, and the right table is the one you're trying to match data with.
SQL OPERATIONS
SQL operations allow you to combine results from two or more SELECT queries, treating the data like sets of numbers, which lets you do things like Union, Intersection, and Difference. Today, we’ll focus on UNION and UNION ALL.
UNION: This combines the results of two queries but removes any duplicate records. A duplicate is any row where all the values are the same in both queries.
Example: Using UNION to Remove Duplicates
Let’s say you have two shops, one in Lekki and one in Marina, and both shops have their own tables listing the sales they made.
SELECT product_name, total_sales FROM Lekki_sales
UNION
SELECT product_name, total_sales FROM Marina_sales;
This query combines the sales data from both shops, but if the same product was sold in both Lekki and Marina, it will only show the product once (because UNION removes duplicates).
You might want to remove duplicates when you only care about seeing unique products sold, without worrying about which shop sold them.
UNION ALL: This also combines the results of two queries, but it keeps the duplicates.
Example: Using UNION ALL to Keep Duplicates
If you want to see all the products sold, even if they were sold in both Lekki and Marina, you’d use UNION ALL.
SELECT product_name, total_sales FROM Lekki_sales
UNION ALL
SELECT product_name, total_sales FROM Marina_sales;
This will show all the products from both shops, even if some products were sold in both places. You’d use UNION ALL when you want to know the full list of products sold, including duplicates.
Wrapping Up
SQL is a powerful tool that makes handling large amounts of data much easier. With SQL JOINS, you can combine different tables, and with SQL OPERATIONS like UNION and UNION ALL, you can decide whether or not to keep duplicates when combining data from multiple sources.
I’m still learning too, so feel free to ask any questions or share any thoughts! It’s all about practice and getting comfortable with how these tools work.
Keep pushing forward, and don’t forget, you’ve got this!
