The SQL JOIN is a command clause that combines records from two or more tables in a database. It is a means of combining data in fields from two tables by using values common to each table.
If you work with databases, you'll likely need to use SQL Joins to retrieve data from multiple tables at some point in your work. These impactful clauses allow you to get information from separate tables so that you get the right information you need to make the best possible decision.
In this guide, you'll learn more about SQL Joins, including the four primary types of Joins, along with their use cases in the real world. Afterward, if you'd like to learn SQL yourself, consider enrolling in the IBM Data Science Professional Certificate.
professional certificate
Prepare for a career as a data scientist. Build job-ready skills – and must-have AI skills – for an in-demand career. Earn a credential from IBM. No prior experience required.
4.6
(80,388 ratings)
758,071 already enrolled
Beginner level
Average time: 4 month(s)
Learn at your own pace
Skills you'll build:
Jupyter, Unsupervised Learning, Regression Analysis, Interactive Data Visualization, Data Literacy, Data Wrangling, Dashboard, Exploratory Data Analysis, Scikit Learn (Machine Learning Library), SQL, Generative AI, Data Analysis, Plotly, Matplotlib, Supervised Learning, Data Mining, Data Visualization, Predictive Modeling, Professional Networking, Data Visualization Software, Machine Learning, Dimensionality Reduction, Classification And Regression Tree (CART), Applied Machine Learning, Machine Learning Algorithms, Feature Engineering, Python Programming, Statistical Analysis, R Programming, GitHub, Git (Version Control System), Development Environment, Cloud Computing, Statistical Programming, Query Languages, Cloud Services, Computer Programming Tools, Big Data, Data Science, Application Programming Interface (API), Other Programming Languages, Version Control, Open Source Technology, Software Development Tools, Data Analysis Software, Pandas (Python Package), Relational Databases, Databases, Data Manipulation, Transaction Processing, Stored Procedure, Data Synthesis, Natural Language Processing, Predictive Analytics, Data Presentation, Data Modeling, Data Ethics, Data Storytelling, Data Cleansing, Descriptive Statistics, Statistical Modeling, NumPy, Data Pipelines, Data-Driven Decision-Making, Data Import/Export, Digital Transformation, Deep Learning, Artificial Intelligence, Scatter Plots, Histogram, Box Plots, Seaborn, Heat Maps, Geospatial Information and Technology, Object Oriented Programming (OOP), Data Structures, Web Scraping, File Management, Restful API, Programming Principles, Computer Programming, Interviewing Skills, Portfolio Management, Applicant Tracking Systems, Talent Sourcing, Problem Solving, Presentations, Job Analysis, Business Research, Recruitment, Communication, Professional Development, Writing, Company, Product, and Service Knowledge, Data Collection, Data Processing, Business Analysis, Data Quality, Decision Tree Learning, Analytical Skills, User Feedback, Peer Review, Stakeholder Engagement, Machine Learning Methods
In Structure Query Language (SQL), a Join is used to connect two or more records within a relational database. As their name suggests, relational databases organize data based on pre-established relationships, which define how data contained in one table relates to data contained within another (or several others).
The Join clause retrieves data from related tables in a database. Because it retrieves data from multiple tables, however, the SQL Join clause is more complex than a simple query that retrieves data from a single table.
Read more: Relational vs. Non-relational Database: The Difference Explained
There are many different use cases for SQL Joins, and they are crucial when mapping out relationships between tables in your database. There are four primary types of SQL Joins that you can use: Inner Join, Left Outer Join, Right Outer Join, and Full Outer Join. Explore these four types of JOINs along with some sample SQL Join clauses below:
Inner Joins combine two tables based on a shared key. For example, if you have a table with a column called "user id" and each user id is unique to each user, this you could join that table to another table with a "user id" column to find the information associated with each user. This example shows how to use an Inner JOIN clause to join two tables:
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; |
---|
Left Outer Joins return all rows from the first table and only the rows in the second table that match. This example shows how to use a Left Outer Join clause to join two tables:
SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.user_id |
---|
Right Joins are logically the opposite of Left Joins—they return all rows from the second table, and only the rows in the first table that match. This example shows how to use a Right Outer Join clause to join two tables:
SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.id = table2.user_id |
---|
Full Joins combine both left and right joins by returning all rows from both tables, as long as there is at least one match between them. This example shows how to use a Full Outer Join clause to join two tables:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.user_id |
---|
In SQL, Cross Join returns every possible combination of rows contained within one table with all of those contained within another table (producing what is known as a "Cartesian Product"). While Cross Join queries are effective for relatively small data sets, they can be overwhelming and inefficient with larger datasets.
SQL Joins are critical to retrieving relevant information from relational databases. Perhaps unsurprisingly, then, there are countless ways that SQL Join clauses can be used to provide workers with actionable information when they need it. Below, we explore some real-world examples:
Imagine a table that stores personal information (name, address, phone number) and another table that stores information related to employee job positions. Suppose each row on the employee table represents a single employee. In that case, it makes sense to store the employees' personal data in another table since an individual may be represented more than once (one row per position as they change roles).
Let's say you need to write an application that shows employee names and addresses along with their current position, previous positions, and hire date. To retrieve this data from the database, you need to join these two tables together—the employee table and the personal information table—using some attributes common between them (such as Employee ID).
Imagine now that you have an online store and want to know which products were bought by your customers. You would have two tables: a customer table, containing information about your customers, and a product table, containing information about your products. You can use an Inner Join to retrieve all the records that appear in both of these tables using the following syntax:
Select * from customers Inner JOIN orders on customers.id = orders.customer_id; |
---|
Consider a situation where you have two database tables, one called “Students” and the other called “Grades.” The “Students” table contains one record for each student: their ID number, name, major, and so on. The “Grades” table contains one record for each student's grade on different courses: their student ID number, the course they took, and their grade in the course.
In SQL, you would write a query to find the names of all students who have received a grade of 100 as follows:
SELECT Students.StudentName FROM Students.
JOIN Grades ON Students.StudentID=Grades.StudentID.
WHERE Grades.Grade=100.
There are many ways to combine results from two or more queries. Here are the most common:
- Use a JOIN statement to combine data from multiple tables in one SELECT statement.
- Use a subquery to retrieve data from one table based on values from another table.
- Use a UNION statement to combine multiple tables (or queries) data.
- A JOIN statement can be used with any other type of statement that SQL supports, including UPDATE and DELETE.
specialization
Get job-ready for your first role in BI. Build the in-demand skills you need to get started in business intelligence (BI) in just 2 months. No degree or prior experience required.
4.6
(392 ratings)
11,908 already enrolled
Beginner level
Average time: 2 month(s)
Learn at your own pace
Skills you'll build:
IBM Cognos Analytics, Dashboard, Data Pipelines, Bash (Scripting Language), Shell Script, Extract, Transform, Load, SQL, Linux Commands, Apache Kafka, Databases, Star Schema, Unix Shell, IBM DB2, Business Intelligence, Data Visualization, Relational Databases, Apache Airflow, Stored Procedure, File Management, Data Warehousing, Pandas (Python Package), Jupyter, Data Manipulation, Data Analysis, Transaction Processing, Python Programming, Query Languages, Linux, Unix, Automation, OS Process Management, Software Installation, Command-Line Interface, Scripting Languages, Operating Systems, Unix Commands, Network Protocols, Linux Servers, Data Mart, Data Lakes, Snowflake Schema, PostgreSQL, Data Cleansing, Data Architecture, Data Validation, Database Design, Data Modeling, Data Integration, Database Systems, Data Quality, Interactive Data Visualization, Looker (Software), Data Visualization Software, Business Intelligence Software, Data Presentation, Analytics, Data Processing, Scalability, Real Time Data, Web Scraping, Data Migration, Data Transformation, Performance Tuning, Big Data
If you're looking to do SQL projects or to get a job using SQL, you may need to build your knowledge and skills. Make sure you learn from reliable material, verify that your instructor has advanced competencies in SQL, and read reviews to see how others feel about the course. Here are some of the ways you might consider learning more about SQL:
Many tutorials are available on the internet that can help you learn SQL. These tutorials are often free and provided by competent people in their field. Learning through tutorials requires some planning. If you choose this route, make sure you follow a logical learning structure to learn all the foundational building blocks for working with SQL. For example, you will need a solid understanding of databases.
There are many online courses with which you can learn SQL. Some of these courses are free, and some charge a fee. Some of the paid courses are comprehensive and offer value for money. Courses provide you with a structured learning process and can be an excellent way to build knowledge.
There are plenty of SQL certifications for you to choose from. Certificates allow you to demonstrate to employers that you have passed an examination testing your SQL knowledge and can be particularly helpful if your resume doesn't contain much SQL experience.
specialization
Launch Your Career in Data Science. Learn the fundamentals of database concepts in a cloud environment, get basic skilling in cloud data services, and build their foundational knowledge of cloud data services within Microsoft Azure.
4.6
(697 ratings)
27,353 already enrolled
Beginner level
Average time: 1 month(s)
Learn at your own pace
Skills you'll build:
Database Theory, Data Storage, Microsoft Azure, Database Administration, SQL, Data Warehousing, Data Processing, MySQL, Relational Databases, Cloud Services, Data Lakes, NoSQL, Databricks, Database Systems, Databases, Cloud Storage, Data Store, Azure Synapse Analytics, Power BI, Database Management, Data Security, PostgreSQL, Data Storage Technologies, Data Management, Application Programming Interface (API), Query Languages, Data Visualization Software, Unstructured Data, Cloud Computing, Transaction Processing, Data Analysis, Analytics, Test Planning, Data Pipelines, Big Data, Data Visualization, Extract, Transform, Load, Data Integration, Business Intelligence
Knowing how to use SQL is core to working with data today. If you want to learn more about SQL, consider taking one of the many courses, Specializations, and Professional Certificates on Coursera:
For foundational SQL knowledge, try the University of Michigan's Introduction to Structured Query Language (SQL) course. There, you'll learn how to create a MySQL database step-by-step and explore SQL in greater depth.
To learn SQL for data science, enroll in the IBM Data Science Professional Certificate. Learn the tools, languages, and libraries used by professional data scientists, including Python and SQL, in this beginner-friendly program.
For SQL for data analysis, consider Google's Data Analytics Professional Certificate. There, you'll learn key analytical skills and tools like SQL, R programming, and Tableau.
course
In this course, you'll walk through installation steps for installing a text editor, installing MAMP or XAMPP (or equivalent) and creating a MySql Database. ...
4.7
(5,467 ratings)
248,419 already enrolled
Intermediate level
Average time: 15 hour(s)
Learn at your own pace
Skills you'll build:
MySQL, Relational Databases, Databases, PHP (Scripting Language), Development Environment, Software Installation, Database Design, SQL, Data Modeling
professional certificate
Get on the fast track to a career in Data Analytics. In this certificate program, you’ll learn in-demand skills, and get AI training from Google experts. Learn at your own pace, no degree or experience required.
4.8
(158,272 ratings)
2,877,621 already enrolled
Beginner level
Average time: 6 month(s)
Learn at your own pace
Skills you'll build:
Interactive Data Visualization, Data Literacy, Rmarkdown, LinkedIn, Data Validation, Tableau Software, Professional Development, Sampling (Statistics), Presentations, Stakeholder Communications, Data Ethics, Data Storytelling, Spreadsheet Software, Data Analysis, Data Cleansing, Data Visualization, Ggplot2, Data Visualization Software, Interviewing Skills, Data Presentation, Analytical Skills, Data-Driven Decision-Making, SQL, Google Sheets, Data Processing, Data Management, Data Sharing, Data Transformation, Data Integrity, Data Quality, Sample Size Determination, Communication, Brand Awareness, Prompt Engineering, Generative AI, Problem Solving, R Programming, Integrated Development Environments, Data Manipulation, Data Structures, Programming Principles, Statistical Programming, Tidyverse (R Package), Dashboard, Quantitative Research, Business Analysis, Expectation Management, Web Content Accessibility Guidelines, Business Analytics, Portfolio Management, Research Reports, Artificial Intelligence, Data Collection, Data Security, Relational Databases, Unstructured Data, Data Storage, Databases, Excel Formulas, Pivot Tables And Charts, Analytics, Data Compilation, User Feedback, Data Integration
Editorial Team
Coursera’s editorial team is comprised of highly experienced professional editors, writers, and fact...
This content has been made available for informational purposes only. Learners are advised to conduct additional research to ensure that courses and other credentials pursued meet their personal, professional, and financial goals.