LearnLinux › LearnLinux › CMDS

join Command Examples

Merge Lines from Two Files Based on Common Field

About join Command

The join command is a powerful text processing tool that merges lines from two sorted files based on a common field, similar to a database JOIN operation. It's part of the GNU core utilities and is essential for combining data from multiple sources in shell scripts and data processing pipelines.

Developed as part of early Unix utilities, join operates on the principle that both input files must be sorted on the join field. It performs an inner join by default (only matching records), but can also perform left and right outer joins. Unlike database systems, join works with plain text files, making it perfect for log analysis, CSV processing, and data integration tasks. The command is particularly valuable when working with large datasets where loading into a database would be overkill, or when you need quick ad-hoc data merging in shell scripts.

Critical Requirement: Files Must Be Sorted!

The join command requires that both input files be sorted on the join field. If files aren't sorted, join will produce incorrect results without warning. Always sort your files first using sort command before joining.

Example: sort file1.txt > file1_sorted.txt && sort file2.txt > file2_sorted.txt

Example 1: Basic Inner Join on First Field
By default, join performs an inner join on the first field (whitespace-separated) of both files. Only lines with matching keys appear in output.

employees.txt (sorted):

101 Alice Engineering 102 Bob Marketing 103 Carol Sales 104 Dave Engineering 105 Eve HR
salaries.txt (sorted):
101 75000 102 65000 104 80000 106 70000
join employees.txt salaries.txt
101 Alice Engineering 75000 102 Bob Marketing 65000 104 Dave Engineering 80000

Understanding Basic Join:

  • Default behavior: Inner join - only matching records output
  • First field: Join field is first column by default (101, 102, etc.)
  • Output format: Join field, followed by remaining fields from file1, then file2
  • 103 and 105: Not in salaries.txt, so not in output
  • 106: Not in employees.txt, so not in output
  • Whitespace: Default field separator is space or tab
  • Single join field: Each matching key appears once with combined data
  • Sorted requirement: Both files must be pre-sorted on join field
Database Analogy: This is like SQL: SELECT * FROM employees INNER JOIN salaries ON employees.id = salaries.id. Only records existing in both tables appear.
Example 2: Custom Field Delimiter (-t)
The -t option specifies a custom field delimiter. Essential for CSV files or any data using specific separators like commas, colons, or pipes.
users.csv (sorted):
1001,Alice,alice@example.com 1002,Bob,bob@example.com 1003,Carol,carol@example.com 1004,Dave,dave@example.com
purchases.csv (sorted):
1001,Laptop,1200 1002,Mouse,25 1002,Keyboard,75 1003,Monitor,350 1005,Tablet,400
join -t, users.csv purchases.csv
1001,Alice,alice@example.com,Laptop,1200 1002,Bob,bob@example.com,Mouse,25 1002,Bob,bob@example.com,Keyboard,75 1003,Carol,carol@example.com,Monitor,350

Custom Delimiter Details:

  • -t option: Specify any single character as field separator
  • CSV files: Use -t, for comma-separated values
  • /etc/passwd: Use -t: for colon-separated data
  • Multiple matches: User 1002 has two purchases, appears twice in output
  • Consistent delimiter: Same delimiter used for input and output
  • Output structure: Join field, file1 remaining fields, file2 remaining fields
  • User 1004: No purchases, doesn't appear (inner join)
  • User 1005: Not in users file, doesn't appear
CSV Processing: For true CSV with quoted fields, consider csvjoin from csvkit package. join works for simple CSV but doesn't handle quotes or embedded commas.
Example 3: Join on Different Field Numbers (-1 and -2)
When join fields are in different positions in each file, use -1 and -2 to specify which field to join on for each file.
products.txt (sorted by field 2 - product code):
Laptop A100 Electronics Mouse A200 Accessories Monitor A300 Electronics Keyboard A400 Accessories
inventory.txt (sorted by field 1 - product code):
A100 45 WH-01 A200 150 WH-02 A300 23 WH-01 A500 67 WH-03
join -1 2 -2 1 products.txt inventory.txt
A100 Laptop Electronics 45 WH-01 A200 Mouse Accessories 150 WH-02 A300 Monitor Electronics 23 WH-01

Different Field Positions:

  • -1 2: Join on field 2 of first file (product code in products.txt)
  • -2 1: Join on field 1 of second file (product code in inventory.txt)
  • Field numbering: Fields numbered starting from 1 (not 0)
  • Sort requirement: products.txt sorted on field 2, inventory.txt on field 1
  • Output format: Join field first, then other fields from each file
  • Keyboard (A400): Not in inventory, doesn't appear
  • A500: Not in products, doesn't appear
  • Flexibility: Join fields don't need to be in same position
Sorting Tip: Sort on correct fields: sort -k2,2 products.txt and sort -k1,1 inventory.txt before joining.
Example 4: Left Outer Join (-a 1)
The -a option performs an outer join. -a 1 includes all lines from file1 (left outer join), even if no match in file2.
students.txt (sorted):
S101 Alice Freshman S102 Bob Sophomore S103 Carol Junior S104 Dave Senior
grades.txt (sorted):
S101 A S102 B S104 A
join -a 1 students.txt grades.txt
S101 Alice Freshman A S102 Bob Sophomore B S103 Carol Junior S104 Dave Senior A

Left Outer Join Behavior:

  • -a 1: Include all records from file1 (students.txt)
  • Matched records: S101, S102, S104 appear with grade data
  • Unmatched record: S103 (Carol) appears without grade - no match in file2
  • Missing fields: When no match, file2 fields are simply omitted (not NULL or blank)
  • All file1 data: Every student appears regardless of grade existence
  • Database analogy: SQL LEFT OUTER JOIN
  • Use case: "Show all students, with grades if available"
  • -a 2: Would do right outer join (all records from file2)
Fill Missing Values: Use -e 'N/A' with -o option to display placeholder for missing fields (see Example 7).
Example 5: Full Outer Join (-a 1 -a 2)
Combining -a 1 and -a 2 creates a full outer join, showing all records from both files whether they match or not.
dept_employees.txt (sorted):
D01 Alice D02 Bob D03 Carol
dept_budgets.txt (sorted):
D01 500000 D03 300000 D04 450000
join -a 1 -a 2 dept_employees.txt dept_budgets.txt
D01 Alice 500000 D02 Bob D03 Carol 300000 D04 450000

Full Outer Join Mechanics:

  • -a 1 -a 2: Include unmatched records from both files
  • D01 and D03: Matched - show employee and budget
  • D02: Only in file1 - show department and employee, no budget
  • D04: Only in file2 - show department and budget, no employee
  • Complete picture: See all departments regardless of match status
  • Data quality: Helps identify missing data in either file
  • Database analogy: SQL FULL OUTER JOIN
  • Use case: "Show all departments with whatever data exists"
Data Validation: Full outer joins are excellent for finding data inconsistencies. Missing matches indicate data quality issues or incomplete records.
Example 6: Ignore Case When Comparing (-i)
The -i option makes join case-insensitive when comparing join fields. Useful when data sources have inconsistent capitalization.
locations.txt (sorted case-insensitively):
Boston MA 617 Chicago IL 312 NEW_YORK NY 212 seattle WA 206
offices.txt (sorted case-insensitively):
boston Headquarters CHICAGO Regional Los_Angeles Branch new_york Main
join -i locations.txt offices.txt
Boston MA 617 Headquarters Chicago IL 312 Regional NEW_YORK NY 212 Main

Case-Insensitive Matching:

  • -i flag: Ignores case when comparing join fields
  • Boston vs boston: Match despite different case
  • Chicago vs CHICAGO: Match despite different case
  • NEW_YORK vs new_york: Match despite different case
  • seattle: No match (Los_Angeles not in locations.txt)
  • Output preserves case: Shows original case from file1
  • Sort requirement: Files must be sorted case-insensitively: sort -f
  • Real-world data: User input often has inconsistent capitalization
Sorting for -i: When using -i with join, sort files with sort -f (fold case) to ensure proper ordering: sort -f file.txt | join -i ...
Example 7: Custom Output Format (-o) with Empty Field Replacement (-e)
The -o option lets you specify exactly which fields to output and in what order. Combine with -e to replace empty fields with custom values.
customers.txt (sorted):
C001 Alice 555-1234 C002 Bob 555-5678 C003 Carol 555-9012
orders.txt (sorted):
C001 12.99 C003 45.50
join -a 1 -e 'NO_ORDER' -o 1.2,1.3,2.2 customers.txt orders.txt
Alice 555-1234 12.99 Bob 555-5678 NO_ORDER Carol 555-9012 45.50

Custom Output Format:

  • -o option: Specify exact output fields
  • 1.2: Field 2 from file 1 (name)
  • 1.3: Field 3 from file 1 (phone)
  • 2.2: Field 2 from file 2 (order amount)
  • -e 'NO_ORDER': Replace empty fields with "NO_ORDER"
  • Bob's record: No order exists, shows NO_ORDER instead of empty field
  • Field notation: FILE.FIELD where file is 1 or 2, field is column number
  • Join field omitted: Can exclude join field from output if desired
  • Flexible ordering: Output fields in any order: -o 2.2,1.2,1.1
Report Generation: -o is powerful for creating custom reports. Example: -o 1.2,1.4,2.3,2.5 picks specific columns from each file for clean output.
Example 8: Only Show Unpaired Lines (-v)
The -v option shows only unmatched lines - records that exist in one file but not the other. Perfect for finding missing or orphaned data.
registered_users.txt (sorted):
U001 Alice U002 Bob U003 Carol U004 Dave U005 Eve
active_sessions.txt (sorted):
U001 2024-11-06 U002 2024-11-05 U006 2024-11-06
join -v 1 registered_users.txt active_sessions.txt
U003 Carol U004 Dave U005 Eve
join -v 2 registered_users.txt active_sessions.txt
U006 2024-11-06

Unpaired Lines Analysis:

  • -v 1: Show only lines from file1 with no match in file2
  • -v 2: Show only lines from file2 with no match in file1
  • First command output: Users registered but not currently active (U003, U004, U005)
  • Second command output: Active session with no registered user (U006 - data error!)
  • Data validation: Identify orphaned records or missing relationships
  • Exclusive output: No matched records appear with -v
  • Debugging tool: Find inconsistencies between related datasets
  • Use case: Find users who haven't logged in, or sessions without users
Data Cleaning: Use -v 2 to find foreign key violations - records in child table with no parent. Example: orders without customers, sessions without users.
Example 9: Check if Files are Sorted (--check-order)
The --check-order option verifies that input files are properly sorted. Helpful for catching sort errors that would produce incorrect joins.
unsorted.txt (NOT sorted):
103 Carol 101 Alice 104 Dave 102 Bob
sorted.txt (properly sorted):
101 Manager 102 Staff 103 Staff 104 Intern
join --check-order unsorted.txt sorted.txt
join: unsorted.txt:2: is not sorted: 101 Alice (Error message - join detects unsorted input)
sort unsorted.txt > temp_sorted.txt
join --check-order temp_sorted.txt sorted.txt
101 Alice Manager 102 Bob Staff 103 Carol Staff 104 Dave Intern

Sort Verification:

  • --check-order: Validates input files are sorted (default behavior in newer versions)
  • --nocheck-order: Disables sort checking (not recommended)
  • Error detection: Stops with error if unsorted data detected
  • Line number reported: Shows where sort order violation occurs
  • Prevention: Catches common mistake before producing bad output
  • Silent corruption: Without check, unsorted files produce incomplete/wrong results
  • Best practice: Always sort before join, let check-order verify
  • Performance: Minimal overhead for sort checking
Pipeline Pattern: sort file1.txt | join - <(sort file2.txt) ensures both inputs are sorted. The dash (-) means read file1 from stdin.
Example 10: Real-World Example - Log Analysis
Practical example: Join web server access logs with user database to enrich log data with user information.
access_log.txt (extracted and sorted by user ID):
1001 /api/users 200 0.045 1001 /api/products 200 0.123 1002 /api/orders 200 0.089 1003 /api/users 404 0.012 1005 /api/login 200 0.234
users.txt (sorted by user ID):
1001 alice@example.com Premium 1002 bob@example.com Free 1003 carol@example.com Premium 1004 dave@example.com Free
join -a 1 -e 'GUEST' -o 1.1,2.2,2.3,1.2,1.3,1.4 -t' ' access_log.txt users.txt
1001 alice@example.com Premium /api/users 200 0.045 1001 alice@example.com Premium /api/products 200 0.123 1002 bob@example.com Free /api/orders 200 0.089 1003 carol@example.com Premium /api/users 404 0.012 1005 GUEST GUEST /api/login 200 0.234
join -a 1 -e 'GUEST' -o 1.1,2.2,2.3,1.2,1.3,1.4 -t' ' access_log.txt users.txt | \ awk '$3 == "Premium" {sum += $6; count++} END {print "Avg Premium response time:", sum/count}'
Avg Premium response time: 0.06

Practical Log Analysis:

  • Data enrichment: Add user email and tier to log entries
  • -a 1: Include all log entries, even if user not found
  • -e 'GUEST': Mark unregistered users as GUEST
  • -o custom format: Reorder: UserID, Email, Tier, Endpoint, Status, Time
  • User 1005: Not in database, marked as GUEST
  • User 1004: No log entries, doesn't appear (left join from logs)
  • Pipeline integration: Join output fed to awk for analysis
  • Real-world value: Answer questions like "How do Premium users perform?" or "Who are the guests?"
Production Pattern: grep '2024-11-06' access.log | awk '{print $1,$7,$9,$10}' | sort | join -a 1 -e 'UNKNOWN' - <(sort users.db) - Extract, sort, join, analyze in one pipeline!

Additional join Command Information

📊 Join Types Comparison

Join Type join Options SQL Equivalent Output Includes
Inner Join join file1 file2 INNER JOIN Only matching records
Left Outer Join join -a 1 file1 file2 LEFT OUTER JOIN All file1 + matching file2
Right Outer Join join -a 2 file1 file2 RIGHT OUTER JOIN All file2 + matching file1
Full Outer Join join -a 1 -a 2 file1 file2 FULL OUTER JOIN All records from both files
Anti Join (file1 only) join -v 1 file1 file2 WHERE file2.key IS NULL file1 records without match
Anti Join (file2 only) join -v 2 file1 file2 WHERE file1.key IS NULL file2 records without match

🔧 Complete join Options

Option Description
-1 FIELD Join on field FIELD of file 1
-2 FIELD Join on field FIELD of file 2
-a FILENUM Also print unpaired lines from file FILENUM (1 or 2)
-e STRING Replace empty output fields with STRING
-i Ignore case when comparing fields
-j FIELD Equivalent to -1 FIELD -2 FIELD
-o FORMAT Specify output format (list of file.field)
-t CHAR Use CHAR as field separator
-v FILENUM Only print unpaired lines from file FILENUM
--check-order Check that input is sorted (default)
--nocheck-order Don't check sort order (not recommended)
--header Treat first line of each file as header
-z Use NUL as line delimiter (for filenames with newlines)

💡 Common Use Cases

Log File Enrichment:

  • Add user information to access logs
  • Combine error logs with user sessions
  • Merge transaction logs with customer data
  • Enrich audit logs with employee details

Data Integration:

  • Merge data from multiple CSV exports
  • Combine inventory from different warehouses
  • Join sales data with product catalogs
  • Match customer orders with shipping information

System Administration:

  • Compare /etc/passwd with active sessions
  • Match process lists with expected services
  • Join disk usage stats with mount points
  • Correlate network connections with application logs

Data Validation:

  • Find orphaned records (foreign key violations)
  • Identify missing relationships between datasets
  • Verify referential integrity in flat files
  • Audit data consistency across systems

🔍 Troubleshooting Common Issues

Problem: No output or incomplete results

Solution: Files probably not sorted! Sort both files on join field: sort -k1,1 file1 > sorted1 and sort -k1,1 file2 > sorted2

Problem: "join: file1:X: is not sorted"

Solution: File isn't properly sorted on join field. If joining on field 2: sort -k2,2 file1. If using -t: specify in sort too: sort -t, -k1,1

Problem: Unexpected whitespace in output

Solution: join uses space as default delimiter for both input and output. Use -t to specify delimiter explicitly. For tabs: join -t$'\t'

Problem: Join fields have leading/trailing spaces

Solution: Clean data first with: sed 's/^ *//; s/ *$//' file.txt to remove leading/trailing spaces before sorting and joining.

Problem: Case sensitivity causing mismatches

Solution: Use -i flag for case-insensitive matching. Remember to sort with sort -f (case-folding) beforehand.

Problem: Need to join CSV with quoted fields

Solution: join doesn't handle CSV quoting. Use csvjoin from csvkit package, or process with python/awk to remove quotes first.

📚 Related Commands

  • sort: Sort files before joining (essential prerequisite)
  • comm: Compare sorted files line by line, show common/unique lines
  • paste: Merge files side-by-side without matching (concatenate columns)
  • awk: More powerful text processing, can do complex joins with associative arrays
  • csvjoin: CSV-aware join from csvkit package
  • sqlite3: For complex queries, load data into SQLite and use SQL JOIN
  • jq: JSON processor with join capabilities for JSON data
  • diff: Compare files, though not for joining

🎯 Best Practices

  • Always sort first: Use sort before join - this is mandatory, not optional
  • Sort on correct field: sort -k2,2 for field 2, sort -t, -k1,1 for CSV
  • Test with sample data: Run join on first 10 lines to verify behavior before processing large files
  • Use --check-order: Let join verify sort order to catch errors early
  • Consider alternatives: For complex operations, awk or database might be better
  • Document field positions: Comment which field is join key in scripts
  • Handle missing data: Use -a and -e appropriately for outer joins
  • Pipeline friendly: join works great in pipelines with sort and awk
  • Beware of memory: join reads file2 into memory - first file should be larger for efficiency
  • Keep it simple: join works best for straightforward merges; complex logic → awk/Python

⚡ Performance Tips

  • File order matters: Put smaller file as file2 (loaded into memory)
  • Pre-process once: Sort and save sorted files if joining multiple times
  • Use locale C: LC_ALL=C sort and LC_ALL=C join for faster processing
  • Numeric sorts: Use sort -n for numeric join keys
  • Parallel processing: Split large files, join in parallel, merge results
  • Consider alternatives: For very large datasets (GB+), database is faster
  • Compression: Can use process substitution: join <(zcat file1.gz) <(zcat file2.gz)
  • Buffering: Use stdbuf -o L for line-buffered output in long pipelines

🔗 Common Patterns

Sort and join in one command:

join <(sort file1.txt) <(sort file2.txt)

Join CSV files:

join -t, <(sort -t, file1.csv) <(sort -t, file2.csv)

Find orphaned records:

join -v 1 <(sort orders.txt) <(sort customers.txt)

Create custom report:

join -a 1 -e 'N/A' -o 1.2,1.3,2.2,2.4 file1 file2

Count matches vs non-matches:

echo "Matched: $(join f1 f2 | wc -l)"
echo "Unmatched in f1: $(join -v 1 f1 f2 | wc -l)"