Linux join Command: File Joining

Introduction

In this lab, you will learn how to use the join command in Linux to merge data from multiple files. We'll simulate a scenario where you're working in a company's HR department and need to combine employee information from different databases. This practical exercise will demonstrate the power and flexibility of the join command for data processing tasks.

Skills Graph

Understanding the Data Files

Let's start by examining the contents of our employee data files. We have two files: employees.txt and salaries.txt.

First, change to the project directory:

cd /home/labex/project

This command changes your current working directory to /home/labex/project. All subsequent operations will be performed in this directory.

Now, let's view the contents of employees.txt:

cat employees.txt

The cat command displays the contents of the file. You should see output similar to this:

1001 John Engineering
1002 Sarah Marketing
1003 Mike Sales
1004 Emily HR
1005 David Finance

Each line represents an employee with their ID, name, and department.

Next, let's look at salaries.txt:

cat salaries.txt

The output should be similar to:

1001 75000
1002 65000
1003 70000
1004 60000
1005 80000

This file contains employee IDs and their corresponding salaries.

These files represent separate databases that we'll merge using the join command.

Basic Join Operation

Now that we understand our data, let's use the join command to merge the information from both files based on the employee ID.

Run the following command:

join employees.txt salaries.txt

You should see output like this:

1001 John Engineering 75000
1002 Sarah Marketing 65000
1003 Mike Sales 70000
1004 Emily HR 60000
1005 David Finance 80000

Let's break down what happened:

1. The join command looked at the first field (employee ID) in both files.
2. When it found matching IDs, it combined the lines from both files.
3. The output shows the employee ID, followed by all fields from employees.txt, and then all fields from salaries.txt (except the ID, which would be redundant).

This gives us a complete view of each employee's information in one line. The join command is particularly useful when you have data split across multiple files but want to view or analyze it together.

Customizing the Join Output

Sometimes, we might want to change the order of the fields in our output or only include specific fields. We can do this by specifying field numbers after the join command.

Try this command:

join -o 1.2,1.3,2.2,1.1 employees.txt salaries.txt

You should see:

John Engineering 75000 1001
Sarah Marketing 65000 1002
Mike Sales 70000 1003
Emily HR 60000 1004
David Finance 80000 1005

Let's break down the -o option:

• -o stands for "output format"
• 1.2 means the second field from the first file (name)
• 1.3 means the third field from the first file (department)
• 2.2 means the second field from the second file (salary)
• 1.1 means the first field from the first file (employee ID)

This allows us to customize the order of information in our output. It's particularly useful when you need to rearrange data for a specific report format or when you only need certain fields from each file.

Handling Unmatched Records

In real-world scenarios, you might have records in one file that don't have corresponding matches in the other file. Let's simulate this by adding a new employee who hasn't been assigned a salary yet.

Add this employee to employees.txt:

echo "1006 Alex IT" >> employees.txt

The >> operator appends the new line to the end of the file without overwriting existing content.

Now, if we run our basic join command:

join employees.txt salaries.txt

You'll notice that Alex doesn't appear in the output because there's no matching record in salaries.txt.

To include unmatched records, we can use the -a option:

join -a 1 employees.txt salaries.txt

Now you should see Alex in the output:

1001 John Engineering 75000
1002 Sarah Marketing 65000
1003 Mike Sales 70000
1004 Emily HR 60000
1005 David Finance 80000
1006 Alex IT

The -a 1 option tells join to include unpairable lines from the first file (employees.txt). This is useful when you want to see all records from one file, even if they don't have matches in the other file.

Joining on a Different Field

So far, we've been joining our files based on the first field (employee ID). But what if we want to join based on a different field? This can be useful when your files are organized differently or when you need to merge data based on a common attribute other than an ID.

Let's create a new file called departments.txt with department codes:

cat << EOF > departments.txt
Engineering ENG
Marketing MKT
Sales SLS
HR HRS
Finance FIN
IT ITS
EOF

This command creates a new file departments.txt with department names and their corresponding codes.

Now, let's join this with our employees.txt file based on the department name:

join -1 3 -2 1 employees.txt departments.txt

You should see:

Engineering 1001 John ENG
Marketing 1002 Sarah MKT
Sales 1003 Mike SLS
HR 1004 Emily HRS
Finance 1005 David FIN
IT 1006 Alex ITS

Let's break down the command:

• -1 3 tells join to use the third field from the first file (employees.txt) as the join field
• -2 1 tells it to use the first field from the second file (departments.txt) as the join field

This allows us to join files based on any common field, not just the first one. It's particularly useful when your files have different structures but share some common information.

Summary

In this lab, you learned how to use the join command in Linux to merge data from multiple files. We explored several key features of join:

• Basic joining of two files based on a common field
• Customizing the output order using the -o option
• Including unmatched records with the -a option
• Joining on fields other than the first column using the -1 and -2 options

Additional join options not covered in this lab include:

• -t CHAR: Use CHAR as input and output field separator
• -i: Ignore differences in case when comparing fields
• -e STRING: Replace missing input fields with STRING
• -j FIELD: Equivalent to '-1 FIELD -2 FIELD'
• -v FILE_NUMBER: Like -a FILE_NUMBER, but suppress joined output lines

The join command is a powerful tool for data processing in Linux, allowing you to combine information from multiple sources efficiently. As you continue to work with data in your Linux environment, remember that join can be a valuable asset for merging and analyzing information from different files.

Resources

• Linux Roadmap - roadmap.sh