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):
This join() method produces the below MySQL query as returned by $db->getLastQuery():
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:
This too executes an INNER JOIN query on the server:
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:
The Query Builder ‘left’ join() method variant produces a MySQL LEFT JOIN.
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:
Executing the join() method with the ‘right’ 3rd parameter produces a RIGHT JOIN MySQL 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:
If you found value in this post, you can show your appreciation and buy me a coffee. It is my favorite drink!
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:
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:
- OpenLampTech issue #15 – CodeIgniter 4 select() methods in depth
- How to Retrieve MySQL Last Insert ID in CodeIgniter 4
- MySQL Column Aliases using the AS keyword
Thank you for reading this post. Please share it with someone else who would enjoy it as well.
Josh Otwell has a passion to grow as a PHP Developer, SQL expert, and technical blogger/writer.
Disclaimer: The majority of examples in this post, are performed in a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Like always, just because you can do something doesn’t mean you should. My opinions are my own.
More ways I can help
- Need hosting for your next web application or WordPress site? I highly recommend Hostinger and use them to host my niche bass fishing site. The service is second to none.
- 🔒5 Truths I’ve Come To Realize As a Self-taught Developer
- Desktop and mobile wallpapers, digital downloads, photography services, Shopify and WooCommerce customizations, and content writing – all in one E-commerce Shop. Find your next digital purchase today!
- Take your Laravel applications next level with Battle Ready Laravel by Ash Allen. Learn how to improve the performance, maintainability, and security of your Laravel projects in this e-book.
Disclosure: Some of this blog post’s services and product links 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 this blog post’s services and product links 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.