Softpanorama

May the source be with you, but remember the KISS principle ;-)
Contents Bulletin Scripting in shell and Perl Network troubleshooting History Humor

join command

News Syntax Recommended Links Sorting algorithms Recommended Papers Rcut Reference Pipes
Perl re-implemenations uniq sort tr AWK Tips Humor Etc

The external join command performs the relational database operation "join" of two files. Files are joined on a common key field (column) that should exist in both files. Also both files must be sorted on the key field in the same order. join examines one line at a time from each file. If a certain segment of the lines match, they are combined into one line. Only one instance of the same segment is printed. The files are assumed to be sorted in the same order.

During join operation if the contents in the key field of both files match, the two lines are merged. The merged line is written to the standard output.

Related command are awk, cut, grep, paste, sort, and uniq.  

The join is a classic relational database operation. The join command reads the first line of the first file, then reads lines from the second file until the contents in the key field of the second file match or exceed the contents in the key field of the first file. If a match occurs, the line from the first file and the line from the second file are combined and displayed on the output. If no match occurs, join begins the process again except reading is done from the first file. This process continues until one or both files have been read to the end and no more lines in the other file can match the last line of the file.

An example of a join command might help clarify how the join function works. Let's assume we have the following files.

file1:   file2:  
01123 HP 9000/825 01123 Hewlett Packard
02213 Sun 3/110 02213 Sun Microsystems
03321 Sun 4/110 03321 Sun Microsystems
08412 HP 9000/835 08412 Hewlett Packard

The following command should produce the combined output as shown.

$ join -t"\t" file1 file2

01123 HP 9000/825 Hewlett Packard
02213 Sun3/110 Sun Microsystems
03321 Sun4/110 Sun Microsystems
08412 HP 9000/835 Hewlett Packard

Following is the general format of the join command.

     join [ -an ] [ -e str ] [ -jn m ] [ -o list ] [ -tc ] file1 file2

Options

The following list describes the options and their arguments that may be used to control how join functions.

-an If a field in file n does not have a match in the other file, the line is written to file n. For example, if you use
    join -a1 file1 file2
  and file1 has a line in it with a key field that is not matched in file2, then that line is written to the file named 1.
-e str Replaces empty output fields with the str string. The -o option causes empty fields in certain conditions.
-jn m The join is performed using the mth field of file n. If you do not specify n, the mth field is used from both files. The fields are numbered beginning with 1.
-o list Specifies the fields to be displayed on output after the join has been performed. The list is comprised of n.m descriptions that define which fields from each file are to be displayed. The n refers to the file name and the m refers to the field number. Multiple n.m descriptions may be used in the list. They must be space separated. An example is,
    join -j1 3 -j2 4 -o 1.1 1.3 2.1 1.5 file1 file2
  which uses field 3 of file1 and field 4 of file2 to join the files. The output is field 1 of file1, field 3 of file1, field 1 of file2, and field 5 of file1.
-tc Character c is used as the input and output field separator. For example,
    join -t: file1 file2
  informs join to use a : (colon) field separator in place of a tab.
  The default input field separators are blanks (spaces, tabs, and new-lines). If the default separators are being used, multiple occurrences count as only one field separator. Leading separators are ignored. The default output separator is a space.

The following list describes the arguments that may be passed to the join command.

- A hyphen may be used in place of file1 to cause join to read the standard input as file1.
file1 The first file to be joined to the second file. The file must be sorted in ASCII collating sequence on the fields that are used to join the file to file2.
file2 The second file to be joined to the first file. The file must be sorted in ASCII collating sequence on the fields that are used to join the file to file1.

If fields are separated by spaces and tabs you need to use the sort -b command for sorting the file. If you use the -t option you need to use the sort command without the -b option.

Unfortunately, the comm, join, sort, and uniq commands have different format conventions for referencing data.

Numeric filenames may conflict with the -o option and the field descriptions that relate to it. One solution is to mv your files to temporary names beginning with a nonnumeric character. Then after the join, move the files back to their original names.

You use the join command to join related files together based on a key field. The output is a subset of the fields and lines from both files. It is useful in combining two related tables (files) together to create one larger table.

The line segment (or field) is chosen using three switches. The -1 switch selects the field number from the first file. The -2 switch selects the field number from the second. The -t switch specifies the character that separates one field from another. If these switches aren't used, join separates fields by spaces and examines the first field on each line.

Suppose the data in the robots.txt file was separated into two files, one with the pricing information (robots1.txt) and one with the quantity and account information (robots2.txt).

$ cat robots1.txt
Birchwood China Hutch,475.99
Bookcase Oak Veneer,205.99
Small Bookcase Oak Veneer,205.99
Reclining Chair,1599.99
Bunk Bed,705.99
$ cat robots2.txt
Birchwood China Hutch,1,756
Bookcase Oak Veneer,1,756
Small Bookcase Oak Veneer,1,756
Reclining Chair,1,757
Bunk Bed,1,757

To join these two files together, use a comma as a field separator and compare field 1 of the first file with field 1 of the second.

$ join -1 1 -2 1 -t, robots1.txt robots2.txt
Birchwood China Hutch,475.99,1,756
Bookcase Oak Veneer,205.99,1,756
Small Bookcase Oak Veneer,205.99,1,756
Reclining Chair,1599.99,1,757
Bunk Bed,705.99,1,757

If either file contains a line with a unique field, the field is discarded. Lines are joined only if matching fields are found in both files. To print unpaired lines, use -a 1 to print the unique lines in the first file or -a 2 to print the unique lines in the second file. The lines are printed as they appear in the files.

The sense of matching can be reversed with the -v switch. -v 1 prints the unique lines in the first file and -v 2 prints the unique lines in the second file.

The tests are case-insensitive when the --ignore-case (or -i) switch is used.

The fields can be rearranged using the -o (output) switch. Use a comma-separated field list to order the fields. A field is specified using the file number (1 or 2), a period and the field number from that file. A zero is a short form of the join field.

$ join -1 1 -2 1 -t, -o "1.2,2.3,2.2,0" robots1.txt robots2.txt
475.99,756,1,Birchwood China Hutch
205.99,756,1,Bookcase Oak Veneer
205.99,756,1,Small Bookcase Oak Veneer
1599.99,757,1,Reclining Chair
705.99,757,1,Bunk Bed

 

Examples

To join the /etc/passwd and /etc/group file together to show the user name, user ID, group name, group ID, group name, and home directory (both files should be sorted in ASCII collating sequence).

1. First you need to sort the passwd file on the group ID and user ID fields:
sort -t':' +3.0n -4.0n +2.0n -3.0n /etc/passwd > mypasswd
2. Sort the group file on the group ID field. sort -t: +3.0n -4.0n /etc/group > mygroup
3. Now you can join two files:.
   > join -t: -j1 4 -j2 3 -o 1.1 1.3 2.1 2.3 1.6 mypasswd
     mygroup
     root:0:0:root
     .
     .
     mylogin:60:40:tech
     .
     .

Top Visited
Switchboard
Latest
Past week
Past month

NEWS CONTENTS

Old News ;-)

join

The join command can be used to merge two files (one can be standard input) to create a third file (can be standard output). Each line in the file is merged on the basis of a field that has the same value in both input files to create one line in the output file. The fields in each file are separated by either a space or tab character.

Following is a list of flags that can be used with the join command:

Examples Let us assume we have two files, file1 and file2, whose contents are shown as follows:

more file1
computer1 16MB 1.2GB 17inch CDROM
computer2 8MB 840MB 14inch
computer3 12MB 1.6GB 17inch
computer4 4MB 270MB 14inch

more file2
computer1 1stfloor office5
computer3 2ndfloor office9A
computer4 1stfloor office2
computer5 3rdfloor office1

If you want to join the two files and display only the matching lines, execute the following command:

join file1 file2
computer1 16MB 1.2GB 17inch CDROM 1stfloor office5
computer3 12MB 1.6GB 17inch 2ndfloor office9A
computer4 4MB 270MB 14inch CDROM 1stfloor office2

If you want to join the two files and display the matching lines as well as the nonmatching lines from the specified file, use the -a flag in the following command:

join -a1 file1 file2
computer1 16MB 1.2GB 17inch CDROM 1stfloor office5
computer2 8MB 840MB 14inch
computer3 12MB 1.6GB 17inch 2ndfloor office9A
computer4 4MB 270MB 14inch CDROM 1stfloor office2

The above example displays the line with computer2 from file1 because it does not have a matching line in file2. If you want to display only the lines that do not match lines from the specified file, use the -v flag in the following command:

join -v2 file1 file2
computer5 3rdfloor office1

The above example displays the line with computer5 from file2 because it does not have a matching line in file1.

If you want to display only certain fields from the input files to the output file, use the -o flag as in the following command:

join -o 1.1 2.2 2.3 1.5 file1 file2
computer1 1stfloor office5 CDROM
computer3 2ndfloor office9A
computer4 1stfloor office2 CDROM

In the above example, the line with computer3 is displayed with one field short because that field is not present in the input file. You can insert a fixed legend in the empty field in the output by using the -e flag in the following command:

join -o 1.1 2.2 2.3 1.5 -e"NO CDROM" file1 file2
computer1 1stfloor office5 CDROM
computer3 2ndfloor office9A NO CDROM
computer4 1stfloor office2 NO CDROM


Etc

FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available in our efforts to advance understanding of environmental, political, human rights, economic, democracy, scientific, and social justice issues, etc. We believe this constitutes a 'fair use' of any such copyrighted material as provided for in section 107 of the US Copyright Law. In accordance with Title 17 U.S.C. Section 107, the material on this site is distributed without profit exclusivly for research and educational purposes.   If you wish to use copyrighted material from this site for purposes of your own that go beyond 'fair use', you must obtain permission from the copyright owner. 

ABUSE: IPs or network segments from which we detect a stream of probes might be blocked for no less then 90 days. Multiple types of probes increase this period.  

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotesSomerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose BierceBernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOSProgramming Languages History : PL/1 : Simula 67 : C : History of GCC developmentScripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-MonthHow to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Haterís Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least


Copyright © 1996-2016 by Dr. Nikolai Bezroukov. www.softpanorama.org was created as a service to the UN Sustainable Development Networking Programme (SDNP) in the author free time. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License.

The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.

FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.

This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...

You can use PayPal to make a contribution, supporting development of this site and speed up access. In case softpanorama.org is down you can use the at softpanorama.info

Disclaimer:

The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the author present and former employers, SDNP or any other organization the author may be associated with. We do not warrant the correctness of the information provided or its fitness for any purpose.

Last modified: September 28, 2017