CodeIgniter 4 Query Builder join() Method Explained

The CodeIgniter 4 Query Builder class provides ORM-like functionality for Database operations in the framework. As developers, we know SQL JOINs are foundational and oftentimes required in order to retrieve the correct data. The Query Builder class has a dedicated join() method you can use when constructing queries where JOINs are necessary. Continue reading and learn how to use the CodeIgniter 4 Query Builder join() method…


The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.


Related: Unsure of what an SQL JOIN is, or the different types of JOINs that are available? I have written a fantastic blog post introducing JOINs, SQL Joins – A Simple Guide.

Housekeeping

I will log and share the MySQL JOINs using the $db->getLastQuery() method so we know the exact queries executed. I wrote a high-level overview, CodeIgniter 4 Query Helper $db->getLastQuery() method for SQL prototyping, you can read for more information about this handy built-in method.

I’ll be using this connection to the well-known Sakila database for the example queries throughout the post:

$db = db_connect('sakilaGroup');
$builder = $db->table('city');

CodeIgniter 4 Query Builder join() method syntax

The Query Builder join() method can be called on either a $builder or model instance:

$builder->join('join_table', on_condition, JOIN type)

The JOIN type parameter is optional and if omitted, defaults to an INNER JOIN.

The available JOIN types are:

  • inner
  • left
  • right
  • outer
  • left outer
  • right outer

πŸ“ At the time of writing, an OUTER JOIN is not supported in MySQL or MariaDB. See the MySQL online JOIN documentation for more information on MySQL JOINs.


Discover premium articles, in-depth guides, instructional videos, and much more by joining the “MySQL Learning Tier” membership. You have access to exclusive content unpublished anywhere else with this membership. With new content being added regularly, continue learning MySQL at any level.


CodeIgniter 4 Query Builder join() method: inner

An INNER JOIN is one of the most common types of JOINs used. This is the default for the join() method. If you omit the 3rd parameter altogether, you will get an INNER JOIN (with the optional INNER keyword removed as it is not required for the default JOIN type):

CodeIgniter 4 Query Builder default join()

This join() method produces the below MySQL query as returned by $db->getLastQuery():

MySQL default JOIN query.

Just as the INNER keyword is optional in a MySQL JOIN query, so too is it optional for the CodeIgniter 4 Query Builder join() method. However, you can include it as the 3rd parameter if you want:

CodeIgniter 4 Query Builder join method inner
CodeIgniter 4 Query Builder join() method with the explicit inner keyword.

This too executes an INNER JOIN query on the server:

MySQL INNER JOIN query
MySQL INNER JOIN query.

Since the INNER keyword is not required for this type of JOIN, both of these example JOINs are equivalent.


πŸ”’ Support my content by unlocking this premium article over on my Kofi page: 5 Truths I’ve Come To Realize As a Self-taught Developer. Thank you! πŸ™πŸ»


CodeIgniter 4 Query Builder join() method: left

While an INNER JOIN may be the most common type of JOIN, the LEFT JOIN is used more often than one would think. In fact, sometimes using a LEFT JOIN is the best option to ensure all data is retrieved, both matching and non-matching rows alike.

In the join() method call, simply specify ‘left’ as the 3rd parameter:

CodeIgniter 4 Query Builder left join()

The Query Builder ‘left’ join() method variant produces a MySQL LEFT JOIN.

MySQL LEFT JOIN query

CodeIgniter 4 Query Builder join() method: right

As in the ‘left’ JOIN variant, you can specify ‘right’ in the join() method call and execute a RIGHT JOIN:

CodeIgniter 4 Query Builder right join()

Executing the join() method with the ‘right’ 3rd parameter produces a RIGHT JOIN MySQL query:

MySQL RIGHT JOIN query

Thank you for being here! Help support my content with a Tip today. πŸ™


CodeIgniter 4 Query Builder join() method – left outer and right outer

With the ‘outer’ JOIN queries, the OUTER keyword is optional. However, you can also include it as part of the join() method call in the 3rd parameter and produce the equivalent MySQL JOIN query:

CodeIgniter 4 Query Builder left outer join()
MySQL LEFT OUTER JOIN query

If you found value in this post, you can show your appreciation and buy me a coffee. It is my favorite drink!


CodeIgniter 4 Query Builder right outer join()
MySQL RIGHT OUTER JOIN query

Bonus: CodeIgniter 4 Query Builder join() method – table aliases

Did you know you can even assign the JOIN tables their own aliases in the join() method call? Well, you can!

As part of the 1st parameter, include the joining table alias and then reference it as part of the ON clause condition in the 2nd parameter:

CodeIgniter 4 Query Builder join() method with table alias
MySQL query with table alias

I find working with the CodeIgniter 4 Query Builder join() method to be easy and seamless when integrating JOINs in my queries. I hope you have learned more about the join() method from this post.


As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated. Thank you for reading.

Similar Reading

In addition to this post, you may also enjoy any of the following articles:


Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients.



To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the β€˜Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.


Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

How can I help you?

Disclosure: Some of the services and product links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Disclosure: Some of the services and product links in this post are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.