DEV Community

loading...
Cover image for How to write a SQL Inner Join

How to write a SQL Inner Join

terrythreatt profile image Terry Threatt ・2 min read

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

Platforms Table:

CREATE TABLE platforms (
id INTEGER PRIMARY KEY,
name TEXT);
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)

pic
Editor guide