How to Execute an Inner Join in Django

To showcase the interconnected data from multiple tables in your Django application, you may encounter the need to perform an inner join operation. By leveraging the select_related method, you can effortlessly achieve this.

Consider the following scenario: you want to display a publication's city, state, and country names in an HTML template. However, these details are stored in separate tables. To retrieve this information using an inner join, you can utilize the select_related method as follows:

pubs = publication.objects.select_related('country', 'country_state', 'city')

This query will generate an SQL statement similar to the following:

SELECT "publication"."id", "publication"."title", ..., "country"."country_name", ...  
FROM "publication" 
INNER JOIN "country" ON ( "publication"."country_id" = "country"."id" ) 
INNER JOIN "countrystate" ON ( "publication"."countrystate_id" = "countrystate"."id" ) 
INNER JOIN "city" ON ( "publication"."city_id" = "city"."id" ) 

The retrieved values will be automatically converted into ORM model instances. This allows you to access the related table values through their respective objects within a loop, as demonstrated below:

{% for p in pubs %}
     {{ p.city.city_name}}  # p.city has been populated in the initial query
     # ...
{% endfor %}

By employing this technique, you can efficiently fetch data from multiple tables and present interconnected information in your HTML templates, while avoiding additional database hits for pre-selected forward relations.