Once you are comfortable with basic select statements, your next step is now combining data using joins and narrowing it down(fitering) to exactly what you need using statements like order by , group by and the sort.
JOINS
You can have multiple tables in your database eg Customers table and orders table both from the same parent data. Now customers table holds customers info while orders table hold orders info from your parent data. A data analyst can pull related data between the two tables using the joins operations in an sql querry.
NOTE: for this to happen , there has to be a common column between the two tables. We'll dive deep into that when we get to the syntax part.
Now there are 5 major joins in sql:
1. INNER JOIN
Returns only rows that have a match in both tables.
syntax:
select c.name, o.product_id, o.quantity
from duka.duka_customers c
inner join duka.duka_orders o
on c.customer_id = o.customer_id;
We have 2 tables sharing a common column customer_id. The column name comes from the table duka_customers.
You can tell that from the alias c, while product_id and quantity come from the table duka_orders.
An Alias is like a pointer here to show you which column comes from which table.
2. LEFT JOIN(LEFT OUTER JOIN)
Returns all rows from the left table plus matching data from the right table. Rows with no match get NULL for the right table's columns.
syntax:
select p.product_name, o.order_id
from duka.duka_products p
left join duka.duka_orders o on p.product_id = o.product_id;
From the syntax above , we are basically trying to see all the products from the table duka_products along side their order_ids from the table duka_orders.
3. RIGHT JOIN(RIGHT OUTER JOIN)
The mirror image of a LEFT JOIN — returns all rows from the right table, with matches from the left table where available.
4. FULL OUTER JOIN
Returns all rows from both tables, matching where possible and filling with NULL where there's no match on either side. Useful for spotting mismatches between two datasets.
5. SELF JOIN
A table joined to itself — common for hierarchical data, like employees and their managers stored in the same table.
This is the original table.
syntax:
select
e.employee_name as employee,
m.employee_name as manager
from public.Company e
left join public.Company m on e.manager_id = m.employee_id;
This is the table after running the self join querry above.
FILTERING
A where clause comes a long way when you want to narrow down your results. Let's take a look at this syntax we had used earlier.
select p.product_name, o.order_id
from duka.duka_products p
left join duka.duka_orders o on p.product_id = o.product_id
where o.order_id is NULL;
Without the where clause, all product names will be returned alongside their order_ids, but after the where clause, only the products whose order_ids are NULLS will be returned.
NOTE: notice how we have used is NULL instead of = NULL
to identify NULLS. A simple explanation is, NULL is not a value, it is a place holder to show you that a certain value is missing.
Common filtering tools:
AND / OR — combine multiple conditions
NOT — negate a condition
IN — match against a list of values
BETWEEN — match a range
LIKE — pattern matching with wildcards (% for any characters, _ for a single character)
IS NULL / IS NOT NULL — check for missing values
syntax:
SELECT *
FROM customers
WHERE country IN ('Kenya', 'Nigeria', 'Ghana')
AND email IS NOT NULL;
HAVING — filtering after aggregation
WHERE can't filter on aggregate results (like a COUNT or SUM), because those values don't exist until after grouping. That's where HAVING comes in:
syntax:
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
This returns customers with more than 5 orders. You cant say where count(*) > 5.
To conclude...
This is should be your Step no. 2 in learning SQL after understanding the basics including the command categories. The Next part will now be Operations to help you transform and calculate the data.
Top comments (0)