When you are new to writing SQL queries, A big hurdle is learning to gather necessary data with complex queries. A common hurdle you will see is an Inner Join.
The Inner Join is a query that will return results that has matching values in two tables. Let's code up a quick stocks tutorial to demonstrate an Inner Join.
First, we are going to initialize a new database to make SQL queries.
Setting up the database:
sqlite3 stocks_database.db
Next, we will need to create two tables so that we can create our inner join.
Stocks Table:
CREATE TABLE stocks (
id INTEGER PRIMARY KEY,
company TEXT,
ticker TEXT,
price INTEGER,
platform_id INTEGER);
Platforms Table:
CREATE TABLE platforms (
id INTEGER PRIMARY KEY,
name TEXT);
Let's create some data so that we can tie together our stock example for our inner join.
Starting Data:
INSERT INTO platforms (name) VALUES ("Robinhood");
INSERT INTO platforms (name) VALUES ("Webull");
INSERT INTO stocks (company, ticker, price, platform_id) VALUES ("GameStop", "GME", 63.77, 1);
INSERT INTO stocks (company, ticker, price, platform_id) VALUES ("AMC", "AMC", 6.83, 1);
INSERT INTO stocks (company, ticker, price, platform_id) VALUES ("Nokia", "NOK", 4.22, 2);
INSERT INTO stocks (company, ticker, price, platform_id) VALUES ("Blackberry", "BB" 13.23, 2);
Here is our inner join that will connect our tables to put together a useful table of data.
Inner Join:
SELECT Stocks.company, Stocks.price, Platforms.name
FROM Stocks
INNER JOIN Platforms
ON Stocks.platform_id = Platforms.id;
Now we are able to return a copy of our database query results.
Results:
company price platform
------- ------ ---------
GameStop 63.77 Robinhood
AMC 6.83 Robinhood
Nokia 4.22 Webull
Blackberry 13.23 Webull
Let's chat about SQL
So now you have a quick guide on how to write an inner join in SQL. If you enjoyed this post feel free to leave a comment about your thoughts and experiences working with SQL.
Happy Coding,
Terry Threatt
Top comments (0)