Join queries help combine different tables in a database. They are mostly used to select data between tables. Understanding join queries can be a little confusing because of their various types, but let's dive into examples of them in postgresql.

<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-9252990233452858"
     data-ad-slot="8120921533"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>

We are going to discuss this topic on an example of publishers and readers tables in a blog database. Let's begin with creating these tables.

## Set Up Tables Using Postgresql Bash

- Create Table

For the sake of simplicity, we will not have a lot of columns in the tables. The reader table has two columns, publisher table has three columns. To create a readers and publishers table, you can use the following query.


```sql
CREATE TABLE reader(id INT PRIMARY KEY,name VARCHAR(64));
CREATE TABLE publisher(id INT PRIMARY KEY,name VARCHAR(64),number_of_article INT);
```

- Insert Data Into Tables

Once you create the table, we can insert rows to the tables. We assume that a reader can also be a publisher, which will make understanding join queries clear.


```sql
# reader
INSERT INTO reader VALUES(1,'john');
INSERT INTO reader VALUES(2,'jane');
INSERT INTO reader VALUES(3,'julia');

# publisher
INSERT INTO publisher VALUES(1,'tom',2);
INSERT INTO publisher VALUES(2,'jane',4);
INSERT INTO publisher VALUES(3,'julia',3);
```

## Types of Join Queries In PostgreSQL

Join queries in postgreslq have different types, now, we can get into these types to see why we need join queries with the help of following examples.

### Inner Join

Inner join queries return the matching common values of the tables. For example, if we need users who are both readers and publishers, we can use a select query and an inner join query.

The following query returns the name and number of the articles of people who are both the reader and publisher.


```sql
SELECT reader.name, publisher.number_of_article 
FROM reader INNER JOIN publisher 
ON reader.name = publisher.name;
```

```
-- output
 name  | number_of_article 
-------+-------------------
 jane  |                 4
 julia |                 3
(2 rows)
```

### Left Join

Left represents the first table in a query and this query returns all data in the left table as well as common data in the two tables.


```sql
SELECT reader.name as reader_name, publisher.name as publisher_name
FROM reader 
LEFT JOIN publisher 
ON reader.name = publisher.name;
```

In the example above, the left table is the reader table so that we could get all data in this table and also the common data in the two tables. As John is not a publisher this value has been set as null.


```
-- output
 reader_name | publisher_name 
-------------+----------------
 john        | 
 jane        | jane
 julia       | julia
(3 rows)
```


### Right Join

Nothing changes compared to left join query, we can get all data from right table and common data in two tables.


```sql
SELECT reader.name as reader_name, publisher.name as publisher_name 
FROM reader 
RIGHT JOIN publisher 
ON reader.name = publisher.name;
```

In the example above, the right table is the publisher table so that we could get all data in this table and also the common data in the two tables. As Tom is not a reader this value has been set as null.



```
-- output
 reader_name | publisher_name 
-------------+----------------
 jane        | jane
 julia       | julia
             | tom
(3 rows)
```

<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-9252990233452858"
     data-ad-slot="6344386266"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>

### Full Join

The full join combines the all rows in the left table and the right table. It is set as null for values that are not matching.


```sql
SELECT * FROM reader 
FULL JOIN publisher 
ON reader.name = publisher.name;
```

As Jane and Julia are in two tables, we got these rows. We also get existing information of john from the left table and tom from the right table as seen below.


```
-- output
 id | name  | id | name  | number_of_article 
----+-------+----+-------+-------------------
  1 | john  |    |       |                  
  2 | jane  |  2 | jane  |                 4
  3 | julia |  3 | julia |                 3
    |       |  1 | tom   |                 2
(4 rows)
```

<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-9252990233452858"
     data-ad-slot="6187628078"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>

## Conclusion

In this article, we have covered join queries in PostgreSQL with examples, which are mostly used with foreign keys to select data in combined different tables.

Thank you for reading.


Examples of different types of join queries in Postgresql including inner join, full join, left join and right join 

Join Queries In PostgreSQL with Examples

In many-to-many relationships between tables, rows in one table can be associated with multiple rows in another table. When working with such tables, it is often useful to select rows as an array, so that all associated rows can be accessed and manipulated easily.

In this article, we will explore how to select rows as an array from tables having many-to-many relationships in PostgreSQL.


<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-9252990233452858"
     data-ad-slot="6187628078"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>

## SELECT AS AN ARRAY IN POSTGRESQL

Let's consider two tables - users and courses - that have a many-to-many relationship, where each user can be enrolled in multiple courses and each course can have multiple enrolled users.

To represent this relationship, we create a third table called **user_courses** that contains two foreign keys - **user_id** and **course_id** - that reference the id columns of the users and courses tables, respectively.


## array_agg Function


To select rows as an array from these tables, we can use the array_agg function in PostgreSQL. The array_agg function aggregates values into an array and can be used in combination with the GROUP BY clause to group rows based on a specific column or columns.

Here's an example query that selects all users and their enrolled courses as an array:


```sql
SELECT users.name, ARRAY_AGG(courses.name) AS course_names
FROM users
JOIN user_courses ON users.id = user_courses.user_id
JOIN courses ON user_courses.course_id = courses.id
GROUP BY users.id;
```

The resulting output of the query will be a list of users and their enrolled courses, where the courses are represented as an array. We can also modify the query to select specific columns from the courses table, such as the id column, along with the name column, like this:

```sql
SELECT users.name, ARRAY_AGG(courses.id) AS course_ids, ARRAY_AGG(courses.name) AS course_names
FROM users
JOIN user_courses ON users.id = user_courses.user_id
JOIN courses ON user_courses.course_id = courses.id
GROUP BY users.id;
```

In this modified query, we select the id and name columns from the courses table, and aggregate them into separate arrays called course_ids and course_names, respectively. We can then use these arrays to access and manipulate the enrolled courses for each user.

<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-9252990233452858"
     data-ad-slot="6344386266"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>

## Conclusion

In conclusion, selecting rows as an array from tables having many-to-many relationships in PostgreSQL can be requires. By using the array_agg function in  with the GROUP BY clause, you can easily group and aggregate data into arrays and select them.

Thank you for reading.





Select rows as an array from tables having many-to-many relationships in PostgreSQL using the array_agg function with the GROUP BY clause.

array_agg Function To Get Array In PostgreSQL

Triggers in PostgreSQL are used with functions to perform an operation like delete, update, insert, etc. before or after a query runs. Let's see examples of how to create a trgigger in postgresql.

## Create Function Returning Trigger

Our use case is that when users added a new post to your website, you may want to check its content and publish it automatically after check. So, if the post status is set as published in the admin_check table, the trigger will perform an insertion operation in the posts table.

<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-9252990233452858"
     data-ad-slot="1214437105"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>
<script>
     (adsbygoogle = window.adsbygoogle || []).push({});
</script>

```sql
CREATE OR REPLACE FUNCTION publish_post() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
BEGIN
  -- New represent the row after update
  -- Old represent the row before update
  -- If admin publish the post, insert it into the posts table and delete it from admin_check table
  IF NEW.status = 'published' THEN
    INSERT INTO posts VALUES(OLD.id, OLD.content, NEW.status);
    DELETE FROM admin_check WHERE id = NEW.id;
  END IF;
  RETURN NULL;
  END;
$$;
```

## Create Trigger

In this step, we will create a trigger by specifying when the function above will run. In our case, the function will be executed after updating the status of the new post in the admin_check table.


```sql
CREATE OR REPLACE TRIGGER after_update_status
  -- When status column of the admin_check table was updated, the function publish_post will trigger.
  -- Also You can use the INSERT, DELETE, TRUNCATE operations
AFTER UPDATE OF status 
ON admin_check 
FOR EACH ROW
EXECUTE PROCEDURE publish_post();
```


## Create Trigger Running Before Database Operation In Postgresql

The example above was covering the operation after. If you want to do something before a query, then here is an example of how to update the updated_at column when updated other columns of the row.

<ins class="adsbygoogle"
     style="display:block"
     data-ad-client="ca-pub-9252990233452858"
     data-ad-slot="6187628078"
     data-ad-format="auto"
     data-full-width-responsive="true"></ins>

```sql
-- Function
CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER 
  LANGUAGE plpgsql
  AS
  $$
  BEGIN
    -- Note that, the type of updated_at must be TIMESTAMP
    -- Assign the date, which is date of the update
    NEW.updated_at = CURRENT_TIMESTAMP;
    -- Replace the new row with the old row
    RETURN NEW;
  END;
  $$;

-- Trigger
CREATE OR REPLACE TRIGGER after_update_content
BEFORE UPDATE --Trigger a function when any of the all columns was updated
ON posts
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at();
```

## Conclusion

In this guide, we have shown you how to trigger a query before or after running anohter query in postgresql such as delete, update, insert, etc.

Thank you for reading


Triggers in PostgreSQL to run queries before or after a query perform

Join Queries In PostgreSQL with Examples

array_agg Function To Get Array In PostgreSQL

How To Trigger Query In PostgreSQL