SQL joins are important in every business relying on technology and modern business intelligence.
And for setting up Yurbi, it is a key step the Yurbi Architect needs to build a perfect Yurbi App for their organization.
Below, you’ll learn the basics of SQL joins, including the different join types and when to use them.
What Are SQL Joins?
An SQL join statement combines data or rows from at least two tables based on a common field the tables share.
Joins are often used when a user wants to fetch data from tables with too many relationships between them. With the help of joins, the user can extract data from the tables based on related rows/columns between them.
The following keywords are used to specify SQL join conditions:
- WHERE: Only used for inner joins; can be used to create a join without using the keyword JOIN
- ON: Used with JOIN to identify which columns to link in each table; can be used with all types of joins
- USING: Combined with JOIN; requires the column name to be identified in both tables
- NATURAL: Ensures columns with the same name only appear once; Associated tables must have at least one pair of identically named columns with the same data type; Don’t use the ON keyword in a natural join
- CROSS: Generates a paired combination of each row of the first table with each row of the second table; also known as a cartesian join
Types of SQL Joins
The format of the new table will vary depending on the type of SQL join you use. The following are some of the most common types of SQL joins:
Inner Join
An inner join uses the INNER JOIN keyword. It will select all elements from both tables (as long as a specific condition is satisfied). All records from both tables with shared fields will be combined when you use INNER JOIN.
Here’s a visual example to help clarify:
Table 1.
Table 2.
An SQL join would combine elements of the two tables.
Say you want to use an inner join to create a new table with names, locations, and course IDs. Your query would look like this:
SELECT TABLE1.COURSE_ID, TABLE1.NAME, TABLE1.LOCATION FROM TABLE1
INNER JOIN TABLE2
ON TABLE2.ROLL_NO = TABLE2Course.ROLL_NO;
This would be the new table:
With an inner join, you were able to combine information from the two shared columns in the tables (the Roll Number column) to create a new one.
Left Outer Join
By combining tables with a left outer join, the result would include unmatched rows from the table specified before you used the LEFT OUTER JOIN keyword.
Here’s another visual example.
Table 1:
Table 2:
Say you want to create a new table and include rows from Table 1 that don’t have matching values in Table 2. You also want to include prices that exceed 10.00. You’d run this query:
SELECT PART, SUPPLIER, PARTS.ITEM#, ITEM, PRICE
FROM TABLE1 LEFT OUTER JOIN ITEMS
ON PARTS.PROD#=TABLE2.ITEM#
AND ITEMS.PRICE>10.00;
The new table would look like this:
Rows from Table 2 are only included in the table if their item number matches the item number of a row in Table 1 and the price is greater than 10.00 for that particular row. The rows with a price value lower than 10.00 show up in the join, but the PRICE value is set to null.
You can also use LEFT JOIN for the same result.
Right Outer Join
A Right Outer Join retrieves all the matching records from the tables involved, as well as all non-matching records from the right-hand side table. The un-matching data will take the null value.
Here’s a visual example:
Table 1.
Table 2.
If you want to create a new table with a right outer join, you will use this query:
SELECT Cand.CandidateNumber, Cand.FullName, Cand.BusinessID, Comp.BusinessID, Comp.BusinessName
FROM Candidate Cand
RIGHT OUTER JOIN Business Comp
ON Cand.BusinessID = Comp.BusinessID
The new table would look like this:
The new table has six rows and features matching rows from both tables, as well as non-matching rows from Table 2.
You can also use RIGHT JOIN for the same results.
Full Outer Join
The full outer join combines two tables based on a common column. It selects records with matching values in those columns as well as the remaining rows from both tables.
Here’s a visual example:
Table 1.
Table 2.
You would use this query to combine the tables with a full outer join:
SELECT TABLE1.clinet_id, TABLE1.first_name, TABLE2.amount
FROM TABLE1
FULL OUTER JOIN TABLE2
ON TABLE1.client_id = TABLE2.client;
This is the result:
Cross Join
The cross join (cartesian join) combines each row of one table with each row of another to show all possible combinations.
Here’s a visual example:
Table 1.
Table 2.
You would use this query to complete a cross join of the two tables:
SELECT c.Car_model, c1.Color_name
FROM TABLE1
CROSS JOIN TABLE2
The result would look like this:
The resulting table shows all possible combinations of car models and colors.
Self Join
The self join joins a table to itself. For this to work, the table must contain a column that acts as a primary key, as well as a different column storing values that match with those in the primary key.
Here’s an example:
Employees
To perform a self-join and show the manager of each employee, you would use the following query:
SELECT employee.Id, employee.Name, employee.ManagerId, manager.Name as ManagerName
FROM Employees employee
JOIN Employees manager
ON employee.ManagerId = manager.Id
The new table would look like this:
The self-join allows you to create two separate tables, one for employees and one for managers, using the same data.
How Can Yurbi Help with SQL Joins?
To summarize, Yurbi is a robust solution that enables non-technical users to generate secure reports with ease.
The Architect Module simplifies database schema modeling and table relationships via SQL joins for each Report Type. For those unfamiliar with SQL joins, Yurbi offers professional services to build the app for your data sources. Empower your organization with Yurbi to unlock valuable insights and informed decision-making. Try it today to streamline your data reporting and analysis.
Apart from that, Yurbi also offers a whole range of different capabilities and features, from data visualization to embedded analytics, and modern business intelligence. Using Yurbi can help you digitize and optimize so many steps in your daily business operations.
Also, Yurbi offers competitive, yet affordable pricing points aimed at small and medium-sized businesses wishing to make things much swifter in their daily processes.
Schedule a meeting with us, so we can discuss things further or take advantage of the free live demo sessions we offer to give you a feel of what we can do to improve your business for both the short- and long- term.